Sequelize Many to Many Include fails with column does not exist

Question

I have 2 tables that are using the belongsToMany association through a 3rd table. I would like to query a specific user from the user table with an array of the associated playlists (from the playlists table) included that match the user_id.

Expected query return

{
    user_id: 'id',
    ...
    user_playlists: [{
        playlist_id: 'id',
        playlist_title: 'title',
        playlist_songs:[...],
        ...
    },{...}]
}

I'm actually doing this in sequelize-typescript, but here is the postgresql commands for the setup.

playlists

CREATE TABLE IF NOT EXISTS "playlists" ("playlist_id" VARCHAR(255) , "playlist_title" VARCHAR(255), "playlist_description" VARCHAR(255), "playlist_image" VARCHAR(255), "playlist_songs" INTEGER[], "playlist_createdAt" TIMESTAMP WITH TIME ZONE, "playlist_updatedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("playlist_id"));

users

CREATE TABLE IF NOT EXISTS "users" ("user_id" VARCHAR(255) , "user_firstName" VARCHAR(255), "user_lastName" VARCHAR(255), "user_email" VARCHAR(255), "user_password" VARCHAR(255), "user_favourites" INTEGER[], "user_following" INTEGER[], "user_recentlyPlayed" INTEGER[], "user_createdAt" TIMESTAMP WITH TIME ZONE, "user_updatedAt" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("user_id"));

user_playlist

CREATE TABLE IF NOT EXISTS "user_playlist" ("user_id" VARCHAR(255)  REFERENCES "users" ("user_id") ON DELETE CASCADE ON UPDATE CASCADE, "playlist_id" VARCHAR(255)  REFERENCES "playlists" ("playlist_id") ON DELETE CASCADE ON UPDATE CASCADE, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, UNIQUE ("user_id", "playlist_id"), PRIMARY KEY ("user_id","playlist_id"));

Sequelize query commands

      const result = await User.findByPk(id, {
        raw: false,
        include: [
          {
            model: Playlist,
            where: {user_id: id},
            as: 'user_playlists',
            required: false,
            attributes: ['playlist_id', 'playlist_title'],
          },
        ],
      });

And the generated postgresql query

SELECT "User"."user_id", "User"."user_firstName", "User"."user_lastName", "User"."user_email", "User"."user_password", "User"."user_favourites", "User"."user_following", "User"."user_recentlyPlayed", "User"."user_createdAt", "User"."user_updatedAt", "user_playlists"."playlist_id" AS "user_playlists.playlist_id", "user_playlists"."playlist_title" AS "user_playlists.playlist_title", "user_playlists->UserPlaylist"."user_id" AS "user_playlists.UserPlaylist.user_id", "user_playlists->UserPlaylist"."playlist_id" AS "user_playlists.UserPlaylist.playlist_id", "user_playlists->UserPlaylist"."createdAt" AS "user_playlists.UserPlaylist.createdAt", "user_playlists->UserPlaylist"."updatedAt" AS "user_playlists.UserPlaylist.updatedAt" 
FROM "users" AS "User" 
LEFT OUTER JOIN ( "user_playlist" AS "user_playlists->UserPlaylist" 
INNER JOIN "playlists" AS "user_playlists" 
ON "user_playlists"."playlist_id" = "user_playlists->UserPlaylist"."playlist_id") 
ON "User"."user_id" = "user_playlists->UserPlaylist"."user_id" 
AND "user_playlists"."user_id" = 'H2qAdR0c81c3xGFk5PmgDXKAjis1' 
WHERE "User"."user_id" = 'H2qAdR0c81c3xGFk5PmgDXKAjis1';

Here is the error I'm receiving:

postgresql    | 2020-03-20 15:57:05.976 UTC [58] ERROR:  column user_playlists.user_id does not exist at character 1015

I can confirm that the user_playlist table has the user_id column:

SELECT * FROM user_playlist;
           user_id            |             playlist_id              |         createdAt          |         updatedAt          
------------------------------+--------------------------------------+----------------------------+----------------------------
 H2qAdR0c81c3xGFk5PmgDXKAjis1 | 9cf2e2ed-932b-4e98-bb6a-39c1e324dc09 | 2020-03-20 16:30:08.387+00 | 2020-03-20 16:30:08.387+00

I feel like the query is wrong, after the last AND

If I manually make the following change to the postgresql query:

From:

AND "user_playlists"."user_id" = 'H2qAdR0c81c3xGFk5PmgDXKAjis1' WHERE "User"."user_id" = 'H2qAdR0c81c3xGFk5PmgDXKAjis1';

To:

 AND "user_playlists->UserPlaylist"."user_id" = 'H2qAdR0c81c3xGFk5PmgDXKAjis1' WHERE "User"."user_id" = 'H2qAdR0c81c3xGFk5PmgDXKAjis1';

Then the query works, but how can I get sequelize to do this?

Maybe I've done something wrong in the setup?

By the way I'm on "sequelize": "5.21.5" and postgres:11.6-alpine

Answer 1

remove "where: {user_id: id}," and "required: false,"