Postgres sort results by id's from subquery

Question

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

Answer 1

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
   

Answer 2

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.