Query with nested include and custom join condition

[alekbarszczewski]

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...

[mickhansen]

Querying on the through model/join table is not possible currently unfortuneatly.

[lucaswxp]

I tried querying associations with conditions and it worked:

User.findAll({include: [{model: Post, where: {active: true}}]

INNER JOIN `posts` AS `post` ON `users`.`id` = `post`.`user_id` AND `post`.`active`=true

Shouldn't this be closed?

[mickhansen]

@lucaswxp the issue is about filtering on join table models - You example is just filtering on the target model :)

[lvarayut]

+1 I'm having the same issue. It's would be nice to have this feature.

[mickhansen]

@lvarayut what does your code look like? It's possible to ammend the join conditions with include.where, not overwrite, do you need to overwrite?

[lvarayut]

@mickhansen I want to join two tables using and condition as following:

SELECT * FROM InterviewSummaries as summary JOIN UserProfiles as profile ON 
(summary.StudentId = profile.id AND summary.version = profile.version)

[mickhansen]

@lvarayut i see

summary.StudentId = profile.id is already supported obviously but yes summary.version = profile.version might be an issue, but should not be too hard to implement actually.

Perhaps an API like:

include: [{model: UserProfile, where: {summary: {$field: UserProfile.rawAttributes.version}}]

[mickhansen]

Perhaps you could already do it like:

include: [{model: UserProfile, where: {summary: sequelize.literal('profile.version')}}]

[lvarayut]

Thanks @mickhansen for your quick response. I'ill try it soon. :)

[eclipse1985]

Is querying on the through model/join table now possible with version 3.*?

[janmeier]

@eclipse1985 Yes include: [ { model .., through: { where: .. } }]

[eclipse1985]

Oh thanks, from which version exactly?

[janmeier]

2.0 rc8 appearently :) https://github.com/sequelize/sequelize/blob/master/changelog.md#200-rc8

[eclipse1985]

Ok, it works. But if I has 1:N relationships like:

{
 name: "Test",
 colors:[{
   value:'Red'
  },{
   value:'Green'
 }]
}

and I want to find this item doing:

include:[{
 'as': colors,
 'where': {
   value:'Red'
 }
}

it returns only a part of original object, something like

{
 name: "Test",
 colors:[{
   value:'Red'
  }
}

without other color values...

There is a way to handle this?
I simply want to do: SELECT * WHERE nestedProperty = value that returns all nested fields and objects

[janmeier]

@eclipse1985 Please provide some more context - models, assocations and actual find calls

[alekbarszczewski]

@eclipse1985 I think I know what you mean - this is happening because when you are using condition in include then only rows matching that condition will be joined (only Red colors). Instead you have to first filter parent model (select all parent models that have at least one color with value = "red") and then for each bucket join (include) all colors that belong to it (include WITHOUT condition)

var Sequelize = require('sequelize')

var sequelize = new Sequelize('postgres://postgres:postgres@localhost/sequelize_test')

var Bucket = sequelize.define('Bucket', {
  name: { type: Sequelize.TEXT, allowNull: false }
})

var Color = sequelize.define('Color', {
  value: { type: Sequelize.TEXT, allowNull: false }
})

Bucket.hasMany(Color, { as: 'colors', foreignKey: 'BucketId' })
Color.belongsTo(Bucket, { as: 'bucket', foreignKey: 'BucketId' })

sequelize.sync({ force: true }).then(function(){
  return Bucket.create({ name: 'myBucket' })
}).then(function(bucket){
  var colors = ['red', 'geen', 'blue'].map(function(color){
    return { value: color, BucketId: bucket.id }
  })
  return Color.bulkCreate(colors)
}).then(function(){
  return Bucket.find({
    where: {
      // here you can use custom subquery to select only buckets that has AT LEAST one "red" color
      $and: [['EXISTS( SELECT * FROM "Colors" WHERE value = ? AND "BucketId" = "Bucket".id )', 'red']]
    },
    include: [
      // and then join all colors for each bucket that meets previous requirement ("at least one...")
      { model: Color, as: 'colors' }
    ]
  })
}).then(function(bucket){
  console.log(JSON.stringify(bucket, null, 2)) // output
})

output:

{
  "id": 1,
  "name": "myBucket",
  "createdAt": "2015-09-14T17:38:49.817Z",
  "updatedAt": "2015-09-14T17:38:49.817Z",
  "colors": [
    {
      "id": 1,
      "value": "red",
      "createdAt": "2015-09-14T17:38:49.824Z",
      "updatedAt": "2015-09-14T17:38:49.824Z",
      "BucketId": 1
    },
    {
      "id": 2,
      "value": "geen",
      "createdAt": "2015-09-14T17:38:49.824Z",
      "updatedAt": "2015-09-14T17:38:49.824Z",
      "BucketId": 1
    },
    {
      "id": 3,
      "value": "blue",
      "createdAt": "2015-09-14T17:38:49.824Z",
      "updatedAt": "2015-09-14T17:38:49.824Z",
      "BucketId": 1
    }
  ]
}

This "technique" is quite good for Postgres because EXISTS() operator is very fast in PG, but I am not sure how it will perform for MySQL for example. As a alternative you can use also this method:

Bucket.find({
    where: {
      // subquery that will select all buckets that have color "red"
      id: { $in: Sequelize.literal('(SELECT DISTINCT "BucketId" FROM "Colors" WHERE value = \'red\')') }
    },
    include: [
      // select all colors per each bucket
      { model: Color, as: 'colors' }
    ]
  })

• First "technique" will execute extra query per each bucket in "Buckets" table
• Second "technique" will execute one extra query before selecting from "Buckets" table and joining colors.

---

Edited example: id: { $eq: Sequelize.literal(' -> it should be $in instead of $eq.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[jacinto123]

Is there a way to do this in the latest version, 4.2.1? It seems, "Support for literal replacements in the where object has been removed."

[cesar2064]

I know is not the best solution, but I have solved a similar issue with this implementation:

    models.PartnerProspectModel.findAll({
                where: {
                    status: 0
                },
                include: [{
                        model: models.UserModel,
                        as: 'prospect_user',
                        duplicating: false,
                        required: true,
                        where: {
                            role: 2
                        },
                        include: [
                            {
                                model: models.UserVideoModel,
                                as: 'user_videos',
                                duplicating: false,
                                required: true,
                                include: [
                                    {
                                        model: models.VideoModel,
                                        as: 'video',
                                        where: models._sequelize.literal('NOT (((`prospect_user->user_videos->video`.`total_video_time` * 100) / `prospect_user->user_videos`.`current_time`) < 90)'),
                                        duplicating: false,
                                        required: true
                                    }
                                ]
                            }
                        ]
                    },  ...(more code)

This is with sequelize 4.4.2, I don't know if it is working in previous versions.

[cybercarnage]

@janmeier Thanks. 'through' and 'required: true' helped me.
My Example:
I need 'Sites' only with 'Craft' association, where 'Craft' entry exists and (Site.planExpiredDate > current date).

associate:
SiteCategoryName.belongsToMany(models.Site, {as: 'Sites', through: 'SiteCategory', foreignKey: 'categoryId'});

Site.hasOne(models.CraftSite, { as: 'Craft', foreignKey: 'siteId' });

This worked for me
models.SiteCategoryName.findAll({ through: { where: { planExpiredDate: { $gt: new Date() } } }, include: [ { model: models.Site, as: 'Sites', include: [ { model: models.CraftSite, as: 'Craft', required: true } ] } ], limit: process.env.ADMIN_PER_PAGE // 10 })

[jeremybradbury]

why doesn't this work?

let options = {where: { table: { field: "value" }, anotherTable: {anotherField: "anotherValue"}, include: [anotherTable] };

and instead of all kinds of messy nested JS code to make mess SQL code... 1 JOIN, 2 WHEREs no subqueries easily stacked with AND by default like other native queries

SELECT *
FROM table JOIN anotherTable ON table.fkid = anotherTable.pkid
WHERE table.field = "value" AND anotherTable.anotherField = "anotherValue" ;

...this lib constantly makes me do all kinds of extra loops and object manipulation, strange native static methods AND/OR raw queries.

[cesar2064]

nice suggestion, @jeremybradbury, if you dont like it you are free to use a different one or create your own lib..

[jeremybradbury]

@cesar2064 I know i bitch a lot but I really do appreciate this lib. I was actually in the midst of doing exactly what you suggested before finding Sequelize and moving to it in a time crunch. but right now reporting is all done in raw queries and filtering is becoming mind-numbing.

I am in the middle of building function to format queries like the above into something to filter related tables with. I'll come back with something useful in the next day or so even it uses raws.

[jeremybradbury]

i ended up building a raw filter query of just ids... dynamically adding joins and where sections depending on the object passed in into an array of ids then passed it into the original query like this:

list => {
  let q = [];
  for(i in list) {
    q.push(list[i].id);
  }
  return Model.findAll({where: {id: q}, include: [<fun list of relations>]})

where list is the raw query result

now my brain hurts

[bharatkapse]

I have four tables join by belongsTo relationship. location belongs to city, city belongs to state and state belongs to country. when i search for a location by id and wish to get city, state and country in the response then I include these model in nested include format like the following.

router.get('/getLocation/:id',function(req,res){
location.findOne({
where:{id:req.params.id},
include:[{
model:city,
attributes:['name'],
include:[{
model:state,
attributes:['name'],
include:[{
model:country,
attributes:['name']
}]
}]
}]
}).then(function(Location){
res.json(Location);
});
});

And the response I am getting is like the following

{
"id": "1",
"displayname": "VimanNagar",
"name": "VimanNagar",
"city_id": "3",
"city": {
"name": "Pune",
"state": {
"name": "Maharashtra",
"country": {
"name": "India"
}
}
}
}

How can I get the response as following

{
"id": "1",
"displayname": "VimanNagar",
"name": "VimanNagar",
"city":"Pune",
"state":"Maharashtra",
"country":"India"
}
please help me out here

[BolajiOlajide]

@jacinto123 Were you able to get a fix?

[BolajiOlajide]

I was able to get around this by using this query instead

      const clause2 = 'failed';
      const currentDate = new Date();

      const defaultingLoans = await Loan.findAll({
        offset,
        limit,
        where: {
          [Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."dueDate" < '${currentDate}' AND "Instalments"."status" IN ('${clause1}', '${clause2}'))`)
        },
        include: [Instalment, 'Owner', 'Approver']
      });```

[niyasshefeek]

what to do with this case ?
3 table Join and One table has value Json Type and joining other table using the JSON value.

` SELECT a.idListing,a.listingtitle,a.listingImage,c.name

FROM listing a LEFT JOIN listingcategory b ON a.listingCategory = b.idListingCategory

LEFT JOIN offercateogry c ON JSON_CONTAINS(a.offercategory, CAST(c.idofferCateogry as JSON), '$'); `

[idmega2000]

I had the same issue and I was able to fix it with.

const getWorkflowByActions = await Workflow.findAndCountAll({
  where: {
      [Op.and]: [
        Sequelize.literal(`EXISTS (select workflowId from Actions where
        "workflowId" = "workflow".id AND status=:myStatus AND actor=:actorId)`),
      ]
  },
  order: [['updatedAt', 'DESC']],
  limit,
  offset,
  include: [
  {
    model: Action,
    as: 'actionTaker',
  }],
  replacements: {
    myStatus,
    actorId,
  },
});

What I did here is to run a subquery that get all the workflow id that meets the necessary conditions in the Action table(It's a one to many table).If the condition is met in one of the many data, I select the Id and then use the main query to generate the necessary data base on those workflowid selected from the Actions table.