CODEWITHSUNDEEP
sqlpostgresqlsequelize.js
Francisco Canela
Francisco Canela

Reputation: 1124

Column does not exists in outer join after alias

I have a problem with the next SQL query (generated by sequelize ORM):

SELECT 
    "Publication".*, 
    "user"."id" AS "user.id"
FROM 
    (SELECT "Publication"."club_id" AS "clubId", "Publication"."user_id" AS "userId" FROM "publication" AS "Publication" WHERE ("Publication"."club_id" = '1')) AS "Publication" 
    LEFT OUTER JOIN "user_account" AS "user" ON "Publication"."user_id" = "user"."id";

The resulting error is:

ERROR: column Publication.user_id does not exist

LINE 6: LEFT OUTER JOIN "user_account" AS "user" ON "Publication"."... ^

My intuition of what is happening is that "Publication"."user_id" is being aliased as userId. Then, the query is being aliased as "Publication"(overwriting the label which was previously refering to the table). The left outer join tries to match new the Publication user_id property with user.id, but it is unable because now it is called userId.

Am I correctly understanding the problem?

Upvotes: 1

Views: 520

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

ERROR: column Publication.user_id does not exist

Answer is simple user_id <> userId:

SELECT "Publication".*, 
       "user"."id" AS "user.id"
FROM 
    (SELECT "Publication"."club_id"  AS "clubId"
            ,"Publication"."user_id" AS "userId" 
      FROM "publication" AS "Publication" 
     WHERE "Publication"."club_id" = '1') AS "Publication" 
LEFT JOIN "user_account" AS "user" 
  ON "Publication"."userId" = "user"."id";
                    -- here use column's alias

Upvotes: 3

Related Questions