On Sun, Sep 22, 2019 at 6:30 AM Arup Rakshit <ar@zeit.io> wrote:
SELECT craftsmanships.id, craftsmanships.name, CASE WHEN contractor_skills.user_id IS NULL THEN FALSE ELSE TRUE END AS has FROM "craftsmanships" LEFT JOIN "contractor_skills" ON "contractor_skills"."craftsmanship_id" = "craftsmanships"."id" LEFT JOIN "users" ON "users"."id" = "contractor_skills"."user_id" WHERE (contractor_skills.user_id = 8 OR contractor_skills.user_id IS NULL) ORDER BY "craftsmanships"."id”;
Gives correct result. Not sure if still this query has bug in it.
If you do not understand the query - then it's wrong on its face. You should never run something which you do not understand.
So one should take a step back - make smaller pieces and then combine smaller pieces of logic together to form an answer. If at some point in the future there is a performance issue - then deal with that then - but do not make some fancy multi join query that you do not fully understand.
So in that vein,
Piece 1 = A list of craftsmanship_id for a particular user
Piece 2 - Take piece 1 and compare to the full list of craftsmanship_id
Putting piece 1 into a CTE you end up with something like this.
So you take the two pieces and combine then. Yes you can drop the CTE into the main body - but unless you are certain you are doing it correctly - there is no point doing that. The query parser will do the work for you - so why bother making your life more difficult then it need be.