New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

belongsToMany include scope errors #3700

Closed
ackerdev opened this Issue May 12, 2015 · 4 comments

Comments

3 participants
@ackerdev
Contributor

ackerdev commented May 12, 2015

There seems to be an issue related to belongsToMany associations preventing usage of scopes with include.

Disclaimer: This repro might not be 100% correct. I don't have a way to test this at the moment, unfortunately, so this is kind of pseudo-code adapted from my codebase. Feel free to call me out if it works on your end.

Model setup

var Foo = sequelize.define({});
var Bar = sequelize.define({});
var Baz = sequelize.define({
  status: Sequelize.BOOLEAN
}, {
  scopes: {
    on: { where: { status: true } }
  }
});

Foo.belongsToMany(Bar);
Bar.belongsToMany(Foo);
Bar.hasMany(Baz);
Bar.hasMany(Baz.scope('on'), { as: 'on' });
Baz.belongsTo(Bar);

Problem #1: Association scope does nothing

Foo.find({
  include: {
    model: Bar,
    include: {
      model: Baz,
      as: 'on'
    }
  }
});

This seems to not work at all, ignoring the scope entirely; it never adds a where clause for status = true.

Problem #2: Subquery references undefined alias

Foo.find({
  include: {
    model: Bar,
    include: Baz.scope('on')
  }
});

Foo.find({
  include: {
    model: Bar,
    include: {
      model: Baz,
      where: {
        status: true
      }
    }
  }
});

Both of these cause erroneous SQL, referencing a not-yet-defined field alias. The query looks something a little like the below:

FROM (
  SELECT ...
  FROM [Foo]
  WHERE (
      SELECT ...
      FROM [Baz] AS [Foo.Bar.Baz]
      WHERE [Foo.Bar].[id] = [Foo.Bar.Baz].[bar_id]     # !!! [Foo.Bar] is not defined here
      AND [Foo.Bar.Baz].[status] = 'true'
  ) IS NOT NULL
)

Version: 2.1.0
Dialect: MSSQL

cc: @janmeier

@ackerdev

This comment has been minimized.

Contributor

ackerdev commented May 12, 2015

Setting required: false on the include circumvents this issue, so it looks like scopes are implicitly making the association required.

This does not solve the issues above, but does give me a potential workaround for now.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented May 12, 2015

Hmm yeah include.where sets include.required by default.
association.scope specifically doesn't do that, but there's no way to tell in your case i think.

@janmeier janmeier self-assigned this May 17, 2015

@janmeier janmeier added the bug label May 17, 2015

@janmeier janmeier closed this in 538a095 Jul 25, 2015

@janmeier

This comment has been minimized.

Member

janmeier commented Jul 25, 2015

@ackerdev I fixed the first issue, and couldn't replicate the second one. Can you test on latest master :)?

@ackerdev

This comment has been minimized.

Contributor

ackerdev commented Jul 28, 2015

Unfortunately not; the codebase has since diverged from this so I can't recreate the original problem. If that pseudo-example didn't trigger the issue, it might have been fixed in the scope refactor from 2.1.1.

All I can say is double check that you used findOne as opposed to findAll, and that you checked it on MS SQL dialect.

Otherwise, if I run across it again I'll be sure to report it, but not a lot I can do about it right now.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment