sequelize / sequelize/sequelize

Query with nested include and custom join condition

Open
#1,869 29 comments 15 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
TypeScript
Stars
30.4k
Forks
4.3k
Avg merge
1d 6h
Merged PRs (30d)
68

Description

I am using master.
I prepared gist with 3 files: .js is actual test, .json is json output from test and .sql is SQL generated by Sequelize that makes sequelize to produce invalid result.
https://gist.github.com/alekbarszczewski/e66cbb99f95debf1e4ce

Basically what I want is:

Playlist.find({
            where: { id: 1 },
            include: [
                {
                    model: Post,
                    where: [
                        'EXISTS('+
                        'SELECT "PostTranslations2"."id" '+
                        'FROM "PostTranslations" AS "PostTranslations2"'+
                        'WHERE "PostTranslations2"."lang" = \'en-us\' AND "Posts"."id" = "PostTranslations2"."PostId"'+
                        ')'
                    ],
                    required: false,
                    include: [
                        { model: PostTranslation, where: { lang: 'en-us' }, required: false }
                    ]
                }
            ]   
        })

Unfortunately it does not work (you can see in gist) because my custom join query (EXISTS(...)) is applied to wrong JOIN table. Joins are as follows:

Playlists -> PlaylistsPosts -> *Posts* -> PostTranslations

Is it possible to apply custom join condition to PlaylistsPosts in this query?
Maybe there is other workaround?

I guess I could do something like:

Playlist.find({
   include: [{ model: PlaylistsPosts, include: [{ model: Post }] }]
})

But then I would get result in wrong format -> each playlist would have playlistsPosts and then each playlistsPost would have post attribute...

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the three gist files: the .js reproduction, the .json output, and the .sql generated query; compare the nested include and its joins, especially PlaylistsPosts, Posts, and PostTranslations. Done means the custom EXISTS condition is applied to the intended join while preserving the requested playlist-to-post result shape.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, sql
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
32/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.