Sorting rows by count of a many-to-many associated record

Question

I know there are a lot of other SO entries that seem like this one, but I haven't found one that actually answers my question so hopefully one of you can either answer it or point me to another SO question that is related.

Basically, I have the following query that returns Venues that have any CheckIns that contain the searched Keyword ("foobar" in this example).

SELECT DISTINCT v.*
FROM "venues" v
  INNER JOIN "check_ins"   c  ON c."venue_id"     = v."id"
  INNER JOIN "keywordings" ks ON ks."check_in_id" = c."id"
  INNER JOIN "keywords"    k  ON ks."keyword_id"  = k."id"
WHERE (k."name" = 'foobar')

I want to SELECT and ORDER BY the count of the matched Keyword for each given Venue. E.g. if there have been 5 CheckIns that have been created, associated with that Keyword, then there should be a returned column (called something like keyword_count) with the value 5 which is sorted.

Ideally this should be done without any queries in the SELECT clause, or preferably none at all.

I've been struggling with this for a while and my mind is just going blank (perhaps it's been too long a day) so some help would be greatly appreciated here.

Thanks in advance!

Answer 1

Sounds like you need something like:

SELECT v.x, v.y, count(*) AS keyword_count
FROM "venues" v
  INNER JOIN "check_ins"   c  ON c."venue_id"     = v."id"
  INNER JOIN "keywordings" ks ON ks."check_in_id" = c."id"
  INNER JOIN "keywords"    k  ON ks."keyword_id"  = k."id"
WHERE (k."name" = 'foobar')
GROUP BY v.x, v.y
ORDER BY 3