On Sun, Sep 22, 2019 at 6:30 AM Arup Rakshit <ar@xxxxxxx> 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.
with UserSkills as (
SELECT
craftsmanship_id
FROM
contractor_skills
WHERE
user_id = 3
)
SELECT
CASE WHEN UserSkills.ctraftsmanship_id IS NULL THEN FALSE
ELSE TRUE as has
FROM
craftsmanships
LEFT JOIN
UserSkills
ON
craftsmanships.id = UserSkills.craftsmanship_id
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.
John W Higgins
