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

Model.scope ambiguous columns #3756

Closed
joshuadmatthews opened this Issue May 19, 2015 · 7 comments

Comments

2 participants
@joshuadmatthews

joshuadmatthews commented May 19, 2015

This is a copy of issues #3123 which seems to not be fixed unless I am missing some new configuration to resolve it... I believe it was supposed to be fixed in the scope refactor.

When a model has a scope that contains a where, it will cause all columns referencing that model in the SQL's where clause to be referenced without being prefixed by their table reference. If including an association that also has a column with the name referenced in the where clause, and the query does not subquery for the source model, the query will error due to ambiguous column names.

Example

Here's an example model definition.

var Foo = sequelize.define('Foo', {
status: DataTypes.STRING
}, {
defaultScope: { where: { status: 'active' } }
});

var Bar = sequelize.define('Bar');

Foo.hasMany(Bar);
Bar.belongsTo(Foo);

Here, we query for a specific Foo instance by id, which will cause Sequelize to generate a query that does not use a subquery to retrieve Foo, allowing the where conditions to break out into the main query.

Foo.find({
where: { id: 1 } // This causes the source model (Foo) to not be retrieved via subquery
include: Bar
});
The generated SQL's FROM will be Foo, it will JOIN Bar, and then the where clause will look something like

WHERE id = 1 AND status = 'active'

And this will result in the error Ambiguous column name 'id'.

@janmeier

This comment has been minimized.

Member

janmeier commented May 19, 2015

I am 99.999% certain that this was fixed - which version are you on?

@joshuadmatthews

This comment has been minimized.

joshuadmatthews commented May 19, 2015

I am on master, has the fixed been merged into it yet or do I need to get
the scope refactor tag?

On Tuesday, May 19, 2015, Jan Aagaard Meier notifications@github.com
wrote:

I am 99.999% certain that this was fixed - which version are you on?


Reply to this email directly or view it on GitHub
#3756 (comment)
.

@janmeier

This comment has been minimized.

Member

janmeier commented May 19, 2015

The PR is merged into master.

Your example works fine for me

SELECT `Foo`.`id`, `Foo`.`status`, `Foo`.`createdAt`, `Foo`.`updatedAt`, `Bars`.`id` AS `Bars.id`, `Bars`.`createdAt` AS `Bars.createdAt`, `Bars`.`updatedAt` AS `Bars.updatedAt`, `Bars`.`FooId` AS `Bars.FooId` FROM `Foos` AS `Foo` LEFT OUTER JOIN `Bars` AS `Bars` ON `Foo`.`id` = `Bars`.`FooId` WHERE `Foo`.`id` = 1 AND `Foo`.`status` = 'active';

Please create a PR with a failing test case if you continue to have problems

@janmeier janmeier closed this May 19, 2015

@joshuadmatthews

This comment has been minimized.

joshuadmatthews commented May 19, 2015

Could you point me in the direction of a resource that would explain how to do that? I installed from npm today so I should be on 3.0.0 but definitely still having this issue.

@joshuadmatthews

This comment has been minimized.

joshuadmatthews commented May 19, 2015

I guess maybe my issue is a bit different. When I use a column name in my where clause that matches a column name from one of the includes, the WHERE condition of the query is not missing the qualifiers, but is instead all kinds of wrong. See below. Any ideas what could cause the where clause to be generated that way? I do have multiple levels of include.

WHERE (
foo.deletedat IS NULL
AND
(
foo.id = id = 1
AND
foo.id = anotherPorpertyFromFoo = 'thatPropertiesValue'
AND
foo.id = anotherPorpertyFromFoo = 'thatPropertiesValue'
AND
foo.id = anotherPorpertyFromFoo = 'thatPropertiesValue'
AND
foo.id = anotherPorpertyFromFoo = 'thatPropertiesValue'
)
);

@joshuadmatthews

This comment has been minimized.

joshuadmatthews commented May 19, 2015

Figured this one out, at least for my case. For some reason passing 1 as the id caused all kinds of weird behavior, but changing it to "1" made everything work perfectly. According to janmeier above it should work with the 1 based on the test code, so I guess something specific to my configuration is causing this. Just a heads up if anyone else runs across it as something to check for.

@janmeier

This comment has been minimized.

Member

janmeier commented May 20, 2015

@joshuadmatthews That sounds weird - If you can create a self contained test-case I'd be happy to have a look at it again. Either post it here, or as a pull request

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