sequelize / sequelize/sequelize
Query with nested include and custom join condition
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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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