0

In postgres 11 im implementing something in type of suggesting new tag for user. To achieve that im first counting amount of activities of specific user in specific category. Query looks as follows

select ac.id as cat_id from activities a 
join articles art on art.id = a.article_id
join articles_categories ac on ac.id = art.category_id
where a.user_id = 787
group by ac.id
order by count(*) desc

Now as it returned categories id's from most often used to least often used i would like to use this ids to find other tags that are most often used within this categories, but user dont know them yet. For this i started to write following query:

select count(t.*) as occurence_per_cat, t.id, t.name, ac.id, ac.name 
as cat_name from tags t
join articles_tags at on at.tag_id = t.id
join articles art on art.id = at.article_id
join articles_categories ac on ac.id = art.category_id
group by ac.id, t.id
order by occurence_per_cat desc

Now i would like to somehow use list of categories id's from first query, to sort results of second query first by category id, then by number of occurences, per category. I suspect i may be wrong about entire concept but cant figure this out. If anybody knows way of achieving the same, regardless if with approach i took or any other, it will be greatly appreciated.

Regards

6
  • Have you tried ORDER BY ac.id, occurrence_per_cat?
    – dwhitemv
    Nov 23, 2022 at 20:55
  • @dwhitemv no i didnt, because that would order by ac id ascending/descending order, and i need ac to be determined by number of user activities within category. First query is attempt of finding this out, that needs to be somehow incoporated in sorting in query 2
    – LSM2236
    Nov 23, 2022 at 21:38
  • 1
    Any chance you could set up a fiddle with some sample data? I think I understand what you want, but it'd help to have concrete data to make sure we get there.
    – dwhitemv
    Nov 24, 2022 at 0:11
  • @dwhitemv here it is dbfiddle.uk/opFniaaJ i think it has everything necessary, if i missed anything in it let me know
    – LSM2236
    Nov 24, 2022 at 10:21
  • Can you define "user dont know them (tags) yet"? Or, what is a "known" tag to a user?
    – dwhitemv
    Nov 25, 2022 at 5:28

2 Answers 2

3

I decided to start new queries from scratch to help with the design process. I recorded my steps below. The last query should accomplish what you want, if I've interpreted your question correctly.

Demonstration fiddle

  1. Category popularity for a user

    select user_id,category_id,count(*) AS views from activities ac 
    join articles a on (ac.article_id=a.id) where user_id=1 
    group by user_id,category_id order by count(*) desc
    
  2. Tag popularity by category

    select category_id, tag_id, count(*) as occurrences from articles a 
    join articles_tags_jointable atj on (a.id=atj.article_id) 
    group by category_id, tag_id
    
  3. Bring these two together, where views is the user's category views (from the first query), and occurrences is the tag usage across all articles (from the second). Move the user filter out to the outer query for ease of use; you may need to move it back into the cat_pop CTE, or deference the CTEs, for performance. (PG11 might still have the CTE optimization barrier.)

    with cat_pop as (
        select ac.user_id,a.category_id,count(*) AS views from activities ac 
        join articles a on (ac.article_id=a.id)
        group by user_id, category_id 
    ),
    tag_pop as (
        select category_id, tag_id, count(*) as occurrences from articles a 
        join articles_tags_jointable atj on (a.id=atj.article_id) 
        group by category_id, tag_id
    )
    
    select category_id, tag_id, views, occurrences from tag_pop tp
    join cat_pop cp using (category_id)
    where user_id=1
    order by views desc, occurrences desc
    
3
  • yea so i think there is a misunderstanding about what does it means that "user dont know the tags yet", which made confusion. Except clause can be removed. While i get the general idea, i cant understand from this query how is it ensured that results will be sorted first by most popular category(understood by user made most activities for articles in this category), then by occurence of the tag on all articles of the category? I can see only ordering by occurences.
    – LSM2236
    Nov 26, 2022 at 9:15
  • Please edit your question and add this information. Your specification says "category," not "user's most popular category." Answer updated as per your comments. Fiddle updated as well. Please check the results and verify they are what you need.
    – dwhitemv
    Nov 27, 2022 at 3:18
  • Yes now its doing precisely what it should. I will make a performance comparison and perhaps use your approach. Thnx for all the effort, accepting the answer now
    – LSM2236
    Nov 27, 2022 at 14:10
0

I think i might got this sorted out, leaving query and fiddle for use of others https://dbfiddle.uk/kiWirBhp

select count(t.*) as occurence_per_cat, t.id, t.name, c.id as cat_id, 
c.name as cat_name from tags t
join articles_tags_jointable at on at.tag_id = t.id
join articles art on art.id = at.article_id
join categories c on c.id = art.category_id
group by c.id, t.id, t.name, c.name
order by array_position(array(select c.id  from activities a 
  join articles art on art.id = a.article_id
  join categories c on c.id = art.category_id
  where a.user_id = 1
  group by c.id, c.name
  order by count(*) desc)::int[], c.id), 
  occurence_per_cat desc

i used array constructor to return values from query 1 as an array of ids, then used method array_position, which is checking equality of current result property (c.id, last param in the method) and item in array, in order as they are delivered in the array. Im not yet sure if this couldnt be done better, or how performance of this solution looks like, but it is some way to go.

2
  • Well, that’s one way to do it… I was going to use either row_number() or rank() to get a ranking then join it to the query and use that column as the sort key.
    – dwhitemv
    Nov 24, 2022 at 15:41
  • That sounds good, possibly even better(faster) then my approach. If u can make it work post it. I will accept it so u get some rep for effort :)
    – LSM2236
    Nov 24, 2022 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.