So I'm querying a table districts, that has one to many relationship with the positions table. I want to select the districts that have the positions with particular name AND count ALL districts positions at the same time. Can I do this?
With the current code
SELECT districts.*, COUNT(DISTINCT(positions.id)) as positions_count FROM "districts"
LEFT JOIN positions ON positions.id = districts.position_id
WHERE ("positions"."name" IN ($1, $2)) GROUP BY districts.id ORDER BY positions_count desc, "districts"."name" ASC
If I have 20 positions in some district but only 2 are filtered, positions_count eq 2 as well, I want it to be 20
I've tried to join twice on the same table with an alias but this gives me the same result
SELECT districts.*, COUNT(DISTINCT(positions_to_count.id)) as positions_count FROM "districts"
LEFT JOIN positions ON positions.id = districts.position_id
LEFT JOIN positions AS positions_to_count ON positions_to_count.id = districts.position_id WHERE ("positions"."name" IN ($1, $2)) GROUP BY districts.id ORDER BY positions_count desc, "districts"."name" ASC
Aucun commentaire:
Enregistrer un commentaire