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

Custom-named model fields break when offsetting, ordering, and including hasMany simultaneously. #5985

Closed
DavidMorton opened this Issue May 28, 2016 · 0 comments

Comments

2 participants
@DavidMorton

DavidMorton commented May 28, 2016

What you are doing?

Sequelize doesn't seem to understand the naming of fields in the database outside of a subquery clause when ordering, offsetting and including members from a hasMany relationship. Here is an example:

        var Foo = current.define('foo', {
            fooId: {
              field: 'foo_id',
              type: DataTypes.UUID,
              primaryKey: true
            },
            baseNumber: {
              field: 'base_number',
              type: DataTypes.STRING(25)
            }
          }, {
          timestamps: false
        }),
          Bar = current.define('bar', {
            barId: {
              field: 'bar_id',
              type: DataTypes.UUID,
              primaryKey: true
            },
            fooId: {
              field: 'foo_id',
              type: DataTypes.UUID,
              allowNull: false
            }
          }, {
            timestamps: false
          });

        Foo.hasMany(Bar, {foreignKey: 'foo_id', as: 'bars'})

        return current.sync({force: true})
          .then(function () {
            var options = {
              include: [
                {
                  model: Bar, as: 'bars'
                }
              ],
              offset: 0,
              limit: 50,
              order: [['base_number', 'ASC']]
            };

           // This statement throws an exception.
            return Foo.findAll(options).then(function () {
              console.log('success!')
            })
          });

What do you expect to happen?

I expect a proper data set in return.

What is actually happening?

The output throws. This appears to be because the SQL that happens is the following:

SELECT "foo".*, "bars"."bar_id" AS "bars.barId", "bars"."foo_id" AS "bars.fooId", "bars"."foo_id" AS "bars.foo_id" FROM (SELECT "foo"."foo_id" AS "fooId", "foo"."base_number" AS "baseNumber" FROM "foos" AS "foo" ORDER BY "foo"."base_number" ASC LIMIT 50 OFFSET 0) AS "foo" LEFT OUTER JOIN "bars" AS "bars" ON "foo"."fooId" = "bars"."foo_id" ORDER BY "foo"."base_number" ASC;

This causes postgres to return the following error:

column foo.base_number does not exist

The proper query should be:

SELECT "foo".*, "bars"."bar_id" AS "bars.barId", "bars"."foo_id" AS "bars.fooId", "bars"."foo_id" AS "bars.foo_id" FROM (SELECT "foo"."foo_id" AS "fooId", "foo"."base_number" AS "baseNumber" FROM "foos" AS "foo" ORDER BY "foo"."base_number" ASC LIMIT 50 OFFSET 0) AS "foo" LEFT OUTER JOIN "bars" AS "bars" ON "foo"."fooId" = "bars"."foo_id" ORDER BY "foo"."baseNumber" ASC;

Dialect: any
Database version: 9.5.2.0
Sequelize version: 4.0.0

DavidMorton added a commit to DavidMorton/sequelize that referenced this issue May 28, 2016

Unit tests for the change that ensures that, while selecting from the…
… database, and ordering on a column that has been renamed in the model, while simultaneously offset selecting and including a hasMany relationship, the query doesn't throw. (closes sequelize#5985)

DavidMorton added a commit to DavidMorton/sequelize that referenced this issue May 28, 2016

Fixed an issue where custom-named model fields break when offsetting,…
… ordering, and including hasMany simultaneously. (Closes sequelize#5985)

@DavidMorton DavidMorton changed the title from Custom-named model fields break when offsetting, ordering, and including hasMany simultaneously. to Closes #5964 - Custom-named model fields break when offsetting, ordering, and including hasMany simultaneously. May 28, 2016

@DavidMorton DavidMorton changed the title from Closes #5964 - Custom-named model fields break when offsetting, ordering, and including hasMany simultaneously. to Closes #5985 - Custom-named model fields break when offsetting, ordering, and including hasMany simultaneously. May 28, 2016

@DavidMorton DavidMorton changed the title from Closes #5985 - Custom-named model fields break when offsetting, ordering, and including hasMany simultaneously. to Custom-named model fields break when offsetting, ordering, and including hasMany simultaneously. May 28, 2016

DavidMorton added a commit to DavidMorton/sequelize that referenced this issue May 30, 2016

Closes sequelize#5985 - Cannot query a custom-named field when offset…
…ting, ordering and including hasMany simultaneously.

mickhansen added a commit that referenced this issue Jun 13, 2016

Closes #5985 - Cannot query a custom-named field when offsetting, ord…
…ering and using hasMany simultaneously (v3 pull request) (#5996)

* Closes #5985 - Cannot query a custom-named field when offsetting, ordering and including hasMany simultaneously.

* Attempting to fix BOM marks

@janmeier janmeier closed this Jun 20, 2016

misgina added a commit to ChurchDesk/sequelize that referenced this issue Jul 28, 2016

Closes sequelize#5985 - Cannot query a custom-named field when offset…
…ting, ordering and using hasMany simultaneously (v3 pull request) (sequelize#5996)

* Closes sequelize#5985 - Cannot query a custom-named field when offsetting, ordering and including hasMany simultaneously.

* Attempting to fix BOM marks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment