sequelize: include on hasMany association only returning one of many results when no primary key

Any update on this?

😉

@holmberd I think if you remove the raw: true parameter the response will contain all the objects.

@rttomlinson If you are still facing this issue the workaround is to add primary key field to the table and include that field in your query or make composite unique key and include them in your select query. for ex:

1. Add primary key

{
 'primary_field_id': {
   autoIncrement: true, 
   primaryKey: true
 },
 ...
}

1. Add composite unique key (if possible, so that you don’t have to add useless primary key field as above)

{
  'field1': {
    type: SomeType,
    unique: 'composite_unique'
  },
  'field2': {
    type: SomeType,
    unique: 'composite_unique'
  },
 ...
}

This is how I got things work.

I’m having the same issue.

Code

var User = sequelize.define('User', { name: Sequelize.STRING })
var Task = sequelize.define('Task', { name: Sequelize.STRING })
Task.removeAttribute('id') // This line is causing all the problems

Task.belongsTo(User, { foreignKey: 'User_ID', as: 'User' })
User.hasMany(Task, { foreignKey: 'User_ID' })

User.sync({ force: true })
  .then(function () { return Task.sync({ force: true }) })
  .then(function () {
    return User.create({
      name: 'Foo',
      Tasks: [
        { name: 'Baz' },
        { name: 'Bar' },
        { name: 'Boris' },
        { name: 'Barata' }
      ]
    }, { include: [ { all: true } ] })
  })
  .then(function () {
    User
      .all({ include: [ { all: true } ] })
      .then(function (users) {
        users.map(function (user) {
          console.log(user.toJSON())
        })
      })
  })

Result

Without id it only returns the first row.

{ id: 1, name: 'Foo', Tasks: [ { name: 'Bar', User_ID: 1 } ] }

Commenting the Task.removeAttribute('id') everything works properly.

{ id: 1,
  name: 'Foo',
  Tasks:
   [ { id: 4, name: 'Barata', User_ID: 1 },
     { id: 3, name: 'Boris', User_ID: 1 },
     { id: 2, name: 'Bar', User_ID: 1 },
     { id: 1, name: 'Baz', User_ID: 1 } ] }

SQL

The SELECT queries generated (with and without id):

SELECT "User"."id", "User"."name",                             "Tasks"."name" AS "Tasks.name", "Tasks"."User_ID" AS "Tasks.User_ID" FROM "Users" AS "User" LEFT OUTER JOIN "Tasks" AS "Tasks" ON "User"."id" = "Tasks"."User_ID";
SELECT "User"."id", "User"."name", "Tasks"."id" AS "Tasks.id", "Tasks"."name" AS "Tasks.name", "Tasks"."User_ID" AS "Tasks.User_ID" FROM "Users" AS "User" LEFT OUTER JOIN "Tasks" AS "Tasks" ON "User"."id" = "Tasks"."User_ID";

Identical with the exception of the extra column id.

Final

I also found that the method User.getTasks() works as intended without an Primary Key.

Hard to believe… I just added { plain: false, raw: true } and then I got this:

[ { id: 1,
    createdAt: 2018-05-02T22:57:45.858Z,
    updatedAt: 2018-05-02T22:57:50.965Z,
    'items.id': 1,
    'items.name': 'Test',
    'items.price': '12',
    'items.createdAt': 2018-05-02T22:57:42.861Z,
    'items.updatedAt': 2018-05-02T22:57:42.861Z,
    'items.CartItem.id': 2,
    'items.CartItem.qty': 2,
    'items.CartItem.createdAt': 2018-05-02T22:57:50.984Z,
    'items.CartItem.updatedAt': 2018-05-02T22:57:50.984Z,
    'items.CartItem.CartId': 1,
    'items.CartItem.ProductId': 1 },
  { id: 1,
    createdAt: 2018-05-02T22:57:45.858Z,
    updatedAt: 2018-05-02T22:57:50.965Z,
    'items.id': 1,
    'items.name': 'Test',
    'items.price': '12',
    'items.createdAt': 2018-05-02T22:57:42.861Z,
    'items.updatedAt': 2018-05-02T22:57:42.861Z,
    'items.CartItem.id': 1,
    'items.CartItem.qty': 1,
    'items.CartItem.createdAt': 2018-05-02T22:57:45.881Z,
    'items.CartItem.updatedAt': 2018-05-02T22:57:50.978Z,
    'items.CartItem.CartId': 1,
    'items.CartItem.ProductId': 1 } ]

This is the SQL generated:

SELECT "Cart"."id",
       "items"."id" AS "items.id",
       "items"."name" AS "items.name",
       "items"."price" AS "items.price",
       "items->CartItem"."id" AS "items.CartItem.id",
       "items->CartItem"."qty" AS "items.CartItem.qty",
       "items->CartItem"."createdAt" AS "items.CartItem.createdAt",
       "items->CartItem"."updatedAt" AS "items.CartItem.updatedAt",
       "items->CartItem"."CartId" AS "items.CartItem.CartId",
       "items->CartItem"."ProductId" AS "items.CartItem.ProductId"
FROM "Carts" AS "Cart"
LEFT OUTER JOIN ("CartItems" AS "items->CartItem"
                 INNER JOIN "Products" AS "items" ON "items"."id" = "items->CartItem"."ProductId") ON "Cart"."id" = "items->CartItem"."CartId"
WHERE "Cart"."id" = '1';

And this is the result if I run the query manually:

Now, if I set only { plain: false } the result of calling findOne() will be an array… and without those options it just returns the following:

const options = {
  include: [ {  model: Product, as: 'items' } ],
  where: { id: '1' },
};

Cart.findOne(options)
  .then(data => console.log(data.items.length)); // this will be 1 (but `items` should have 2 rows)

IMHO this is a critical issue because otherwise we can’t be completely confident on this feature, or we’re missing something else?

@mickhansen:

When we process associations we attempt to dedupe each row by either matching primary keys or row uniqueness.

That would be the problem in my case because:

• CartItem has an id as PK
• CartId and ProductId are not PKs
• the association was made by belongsToMany and { through: { model: CartItem, unique: false } } as options

Any chance the task rows are completely equal?

Both rows on the CartItem are referencing the same Cart and Product, so they’re equal on its references but not necessarely identical (they have a qty column with a different value).

Seems like it’s doing row uniqueness check although it shouldn’t necessarily have to do a full check, nto sure.

There’s a way to disable that deduplication/uniqueness check?

Thank you!

the same problem here? anyone?

The best workaround is to not use sequelize

It’s 2019 guys any updates???

Looking into the libraries it seems to stem from the conditional statement else if at this line in the query.

if (this.options.raw) {
      result = results.map(result => {
        let o = {};

        for (const key in result) {
          if (result.hasOwnProperty(key)) {
            o[key] = result[key];
          }
        }

        if (this.options.nest) {
          o = Dot.transform(o);
        }

        return o;
      });
    // Queries with include
    } else if (this.options.hasJoin === true) {
      results = AbstractQuery._groupJoinData(results, {
        model: this.model,
        includeMap: this.options.includeMap,
        includeNames: this.options.includeNames
      },
     ....

• Since options.hasJoin is set to true if the include options is set, see Models. Which means that AbstractQuery._groupJoinData is never run and it is responsible for grouping the associations by callee.

The function takes the result of the query execution and groups
the associated data by the callee.

• At the end before returning the result of the handleSelectQuery method we have this condition:

    // return the first real model instance if options.plain is set (e.g. Model.find)
    if (this.options.plain) {
      result = result.length === 0 ? null : result[0];
    }
    return result;

Assuming options.plain is set on Model.findAll(hard to deduce) which returns first row only result, since it is a SELECT query handled by the querySelectHandler.

Example of results input(see below) for the handleSelectQuery method with raw: true and plain: true set. Returns exactly the same output as its input format after executing the logic inside the conditional options.raw statement. If plain: true is set then only the first item in the array is returned with only a single associating, since groupJoinData was never run. Which is the result I’m seeing.

var results = [
        {
          some: 'data',
          id: 1,
          association: { foo: 'bar', id: 1 }
        }, {
          some: 'data',
          id: 1,
          association: { foo: 'bar', id: 2 }
        }, {
          some: 'data',
          id: 1,
          association: { foo: 'bar', id: 3 }
        }
     ];

I’ve seen people complain about association count giving false result for the has-many and belongs-to-many relations and I would guess that this might be related too. This is just a quick overview and it would be nice to hear from someone who actually knows the code base if this might be what is affecting the results, and what sort of flow the result is meant to have.

I also fix this problem by removing { raw: true} from my Model.findAll function

…So does this mean the “hasMany” functionality doesn’t actually do what it’s supposed to?

any updates ?

any updates?

I just had the same problem. I ended up having to create an useless id column.

by just removing the raw: true - solved my problem

Seems like this one to many mapping has been bugging off a lot of people 😦

Sorry for the notification, Ignore my misclick 🤦‍♀️

I solved this problem by first fetching as a raw sequelize object, then calling .toJSON() on it.

Example: const post = await db.Post.findByPk(postId, { include: [ { model: db.Comment, as: "comments" } ], raw: false, plain: true, nest: true }); console.log(await post.toJSON());

The relationship between Post and Comment is shown below: Post.hasMany(models.Comment, { foreignKey: "post_id", as: "comments" })

I had the same error, added composite pk to multiple columns of model without id, also added primaryKey: true for them, added .removeAttribute('id'), and all includes now works just fine

Still no fix?!

Removing raw:true from outer level worked for me

When we process associations we attempt to dedupe each row by either matching primary keys or row uniqueness.

Any chance the task rows are completely equal? Seems like it’s doing row uniqueness check although it shouldn’t necessarily have to do a full check, nto sure.

I still having this issue, tested on latest v6 and v7 ( SQLITE ) .

And problem seems to be bigger, because it happens even you add an id.

Every “find” function using raw: true and including ( include: 'association' ) any “to many” association ( hasMany or belongToMany ) give these wrong result.

There are a lot of issues related, and since this seems to be the main thread of the issue, it could be renamed to match better the problem. ( ‘raw’ is the main keyword, ‘primary key’ seems not relevant since it happens any ways using it )

Generated SQL statments are the same using raw or not, so is some parsing issue. Have some one some aproach ( with raw: true ) to get the correct data ?? Or some clue to find a fix ?? Or where to look on Sequelize for a fix ??

Not sure if 100% related to this - but we had an issue with a many to many relationship where the solution of using a primary key was found by a coworker in this documentation link https://sequelize.org/master/manual/advanced-many-to-many.html

We defined the ID in the Model as said and it all worked - so maybe it is not exactly an issue, more of a misunderstanding when using sequelize, or how sequelize does things. Just adding it because nobody pointed in the direction of that link and might be helpful.

You probably noticed that the User_Profiles table does not have an id field. As mentioned above,
it has a composite unique key instead. The name of this composite unique key is chosen
 automatically by Sequelize but can be customized with the uniqueKey option:

User.belongsToMany(Profile, { through: User_Profiles, uniqueKey: 'my_custom_unique' });
Another possibility, if desired, is to force the through table to have a primary key just like other 
standard tables. To do this, simply define the primary key in the model:

const User_Profile = sequelize.define('User_Profile', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
    allowNull: false
  },
  selfGranted: DataTypes.BOOLEAN
}, { timestamps: false });
User.belongsToMany(Profile, { through: User_Profile });
Profile.belongsToMany(User, { through: User_Profile });
The above will still create two columns userId and profileId, of course, but instead of setting 
up a composite unique key on them, the model will use its id column as primary key. 
Everything else will still work just fine.

one-many association query caused a problem that duplicate rows appears from left table, how to fix or config that?

Had the same problem. The actual response (observed when using { plain: false, raw: true}) included all data, so I knew it wasn’t a problem with my query.

I had a A.hasMany(B) relationship where the B table had a foreign key that referenced the primary key of A. The primary key of A was an unsigned integer. Changing this to a signed int solved my problem.

@jpierrep @EdByrnee @BartusZak @jembach @masonjwhite @AmbroiseVuaridel @Relequestual @marcelorl - Can some of you please try the PR created by pateketrueke (#9384) and see if it fixes the problem for you?

You can try it by using npm install https://github.com/json-schema-faker/sequelize/tarball/bugfix/merge_nested_associations.

Any update on when this will be addressed?

Could you help me and try #9384 on your codebase to see if it solves your issue?

So much thanks in advance @masonjwhite 🍻

When we process associations we attempt to dedupe each row by either matching primary keys or row uniqueness.

Is there a way to prevent this behavior? Lets say we have branches and employees. An employee can be associated to multiple branches and have multiple supervisors (other employees) in each branch.

Employee model definition:

var EmployeeModel = db.define('Employee', {
    idEmployee: {
        primaryKey: true,
        type: DataTypes.INTEGER
    },
    Name: DataTypes.STRING
}, {
    timestamps: false,
    classMethods: {
        associate: function (models) {

            // ... association with branches (omitted) ...

            EmployeeModel.belongsToMany(models.model('Employee'), {
                through: {
                    model: models.model('Branch_Employee_Supervisor')
                },
                as: 'Supervisors',
                foreignKey: 'employeeId',
                otherKey: 'supervisorId'
            });
        }
    }
});

Branch model definition:

var Branch = db.define('Branch', {
    idBranch: {
        primaryKey: true,
        type: DataTypes.INTEGER
    },
    Name: DataTypes.STRING,
}, {
    timestamps: false,
    classMethods: {
        associate: function (models) {
            // ... association with employees (omitted)...
        }
    }
});

Join table:

var Branch_Employee_Supervisor = db.define('Branch_Employee_Supervisor', {
    branchId: DataTypes.INTEGER,
    employeeId: DataTypes.INTEGER,
    supervisorId: DataTypes.INTEGER
}, {
    timestamps: false
  }
});

If I want every supervisor for a particular employee, I’d do something like:

let employees = await EmployeeModel.findAll({
    include: {
        model: EmployeeModel,
        as: 'Supervisors'
        required: false
    }
});

However, if an employee has the same supervisor in multiple branches I’d only get the supervisor once, which could be fine in some cases. But the branchId information is lost, isn’t it?