Sorting rows by count of a many-to-many associated record
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 Venue
s that have any CheckIn
s 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 CheckIn
s 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 s开发者_开发知识库orted.
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!
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
精彩评论