Include generates subqueries

[mendrik]

Hi,

I'm trying to use .findAll with a condition over an associated model:
model.Person.findAll({
include: [{ model: model.Alias, as: 'aliases', attributes: ['name']}],
where: model.sequelize.and(
model.sequelize.or.apply(this, search),
{published: true}
),
limit: limit
}

with "search" being among other person restrictions: ["'aliases.name' like ?", term+"%"]

However this generates:

SELECT people., aliases.name AS aliases.name, aliases.id AS aliases.id FROM (SELECT people. FROM people WHERE ((firstname like 'eva g%' OR surname like 'eva g%' OR concat(firstname,' ',surname) like 'eva g%' OR 'aliases.name' like 'eva g%') AND people.published=true) LIMIT 4) AS people LEFT OUTER JOIN alias AS aliases ON people.id = aliases.person_id;

Is it somehow possible to get it so, that there would be no subquery and the join is on the outer clause so the alias condition is applied correctly?

I'm not sure if I'm abusing "include" here, since I'm not really interested in the aliases data, just needed to find the correct people.

Cheers,
Andreas

P.S: I'm using 2.0.0-dev9

[mickhansen]

Holy lack of formatting batman.

[mickhansen]

include.where sets include.required = true by default, if required is true on a :M include it generates a subQuery (because of data duplication.

Use required: false together with a where to kill the subquery.

[mickhansen]

Hmm, ignore my previous comment - With the code you are showing you shouldn't be getting a subquery, are you sure you are showing the whole thing?

Also please test against the latest version + master when reporting an issue, it might already be fixed.

[mendrik]

thanks for the quick reply and sorry about the formatting, however I tried to use required: false previously and it resulted in the same issue. I also tried master branch a sec ago and same problem occurs. To simplify my example:

model.Person.findAll({
  include: {model: model.Alias, as: 'aliases', attributes: ['name'], required: false},
  where: ["'aliases.name' like ?", terms[0]+"%"],
  limit: limit
})

And my model definition is quite simple too:

Person.hasMany(Alias, {
    as: 'aliases',
    onDelete: 'cascade'
});

this all results in:

SELECT `person`.*, `aliases`.`name` AS `aliases.name`, `aliases`.`id` AS `aliases.id`
FROM (
  SELECT  `person`.* FROM `people` AS `person` WHERE 'aliases.name' LIKE 'eva g%' LIMIT 4
) AS `person`
LEFT OUTER JOIN `alias` AS `aliases` ON `person`.`id` = `aliases`.`person_id`;

[mendrik]

Btw. I cannot add the where clause to the include, since it would join only on found aliases.

[mickhansen]

You are getting the subquery because of the combination of a LIMIT and a :M include. There's no way around this, a subquery is absolutely required. Unfortuneatly there's no way to tell sequelize that your where condition should be outside the subquery.

Why can't you put the where on the include? You are querying on the joined table, thats what include.where is there for.

Have you tried something like:

model.Person.findAll({
  include: {
    model: model.Alias,
    as: 'aliases',
    attributes: ['name'],
    required: false,
    where: {
      name: {
        like: terms[0]+"%"
      }
    }
  }
  limit: limit
});

[mendrik]

Ok thanks a bunch, skipping the limit does exactly what I want. The join where doesn't work since I need "or" logic there. You guys saved my day! :)

[mickhansen]

@mendrik yeah .or() is a complicated case - we need a way users can tell sequelize whether to place the where on the inner or outer query, not sure how to do that yet though (api wise).

[ralusek]

@mickhansen Hey there, I just encountered an issue where I am including a model with its own where in the include. I've successfully implemented this countless times, but this is a particularly difficult query. In this case, I'm getting missing FROM-clause entry for table, which is an issue typically related to column naming. I have triple checked the raw query in this case, and there is no problem.

So, like the poster here, I need to have the subquery logic have required:true, and this is causing the where logic to go into the JOIN AND, rather than the outer WHERE. If I move the where logic to the outside the include, and reference the nested properties by col name, query is built with WHERE rather than JOIN filtering, and executes fine.

TL;DR, you mention here: "we need a way users can tell sequelize whether to place the where on the inner or outer query," has that been done yet?

[janmeier]

@ralusek You can use $column$ to reference columns from a joined table on the outer query

User.findAll({
  include: [Task],
  where: {
    '$task.name$': 'foobar'
  }
});

SELECT * 
FROM user 
LEFT JOIN task .... ON ..
WHERE task.name = 'foobar'

[ralusek]

@janmeier Thanks. Yea that's actually what I am doing, but I had the logic of my query on what would be the Task in your example, via a scope. It was convenient to be able to have it scoped, but it made it so the query wouldn't work unless I explicitly place the logic inside of the top level where.

[eumpek]

Hello, i am also having the same problem but when i use '$' operator to reference columns from a joined table what happens is that, i get an error because the subquery doesnt include the referenced table.

Here is my query:
SELECT * FROM ( SELECT * FROM tableA WHERE (tableB.id LIKE '%') LIMIT 0, 20 ) AS t LEFT OUTER JOIN tableB ON tableB.id = tableA.id

The strange thing here is that i have another model, where i user both inner and outer joins, and i never get subquery. I can not figure out what is the difference between the two.

[eumpek]

I ended up using
subQuery:false, in my findAndCountAll method.

[tybro0103]

duplicating: false worked for me