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

[mholtzman]

I looked around for this issue and couldn't find it mentioned exactly as I'm seeing it, so apologies in advance if this is a duplicate. The issue is that when I use include like so:

Project.findAll({ include: [{ model: Task }], where: { ... } })

the query is generated correctly and the correct projects are returned, but each contains an array of Tasks that is always of length one (when the Tasks have no primary key), even though many tasks match. I can confirm this using the raw query and verifying the results. If I add a primary key to Task, it then works correctly.

Here are the related definitions, in case there are some clues there.

sequelize.define('Project', {
       ...
    }, {
        timestamps: true,
        underscored: true,
        createdAt: 'created_at',
        updatedAt: 'last_modified',
        tableName: 'projects',
        classMethods: {
            associate: function(models) {
                Project.hasMany(models.Task, { foreignKey: 'projectId', onDelete: 'CASCADE' });
            }
        },

sequelize.define('Task', {
        ...
    }, {
        timestamps: true,
        underscored: true,
        createdAt: 'created_at',
        updatedAt: false,
        tableName: 'tasks'
    });

[mickhansen]

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.

[mholtzman]

Looks like this is occurring when a field in the table is named 'id'. Here's a test case:

const Sequelize = require('sequelize');

const connection = new Sequelize('', '', '', { dialect: 'sqlite' });

const Task = connection.define('Task', {
    taskType: { type: Sequelize.INTEGER, field: 'id' },
    taskName: { type: Sequelize.STRING(50) }
});

const Project = connection.define('Project', {
    id: { type: Sequelize.INTEGER, primaryKey: true }
});

Project.hasMany(Task);

connection.sync()
    .then(() => Project.create({ id: 1 }))
    .then(() => Task.create({ taskType: 10, taskName: 'get food', ProjectId: 1 }))
    .then(() => Task.create({ taskType: 10, taskName: 'take out trash', ProjectId: 1 }))
    .then(() => Project.findAll({ include: [Task] }))
    .then(tasks => console.log(JSON.stringify(tasks, undefined, 2)));

If you remove the field: 'id' clause, this works as expected. Is this just a mapping error on my part?

[mickhansen]

id is the default name for our primary key, so that should just work actually.

[thevtm]

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.

[joeveiga]

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?

[drc-crueber]

👍

Having the exact same problem on a model with the id removed.

[ianfebriyanto]

is this issues has been fixed ?

[felixfbecker]

😉

[coder13]

...So does this mean the "hasMany" functionality doesn't actually do what it's supposed to?

[walexnelson]

I'm seeing similar behavior as @thevtm. As soon as I include the primary key back into the attributes I get the full list. But when that Id is not selected then I only get the first of many, even though all of the records are unique.

[renatoargh]

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

[rttomlinson]

@renatoargh Can you comment more on what you're doing to get the includes to work? I can't seem to figure it out. Thanks

[m0uneer]

Do tables with composite primary keys is affected also? I face a severe trouble here also that results in first row only

[lekhnath]

@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
 },
 ...
}

2. 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.

[uncedric]

I am having this same issue, is there any additional info regarding how to fix it?

[evenfrost]

Any update on this?

[saigalamatya]

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

[holmberd]

My problem is similar but slightly different. I'm fetching the raw data with include: [{ all: true }]. But the returned raw data only returns a single result from the join table, even though multiple results exist in the table.

The product has a many-to-many association with Tags through a join table called product_tag.
The join table has a composite primary key constraint with product_id and tag_id.

Product:
Product.belongsToMany(models.tag, {through: 'product_tag' });
Tag:
Tag.belongsToMany(models.product, {through: 'product_tag' });

db.models.product.findAll({
  raw: true,
  include: [{ all: true }]
} );

[uncedric]

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

[holmberd]

@uncedric it sure will, but I don't need the objects, only the raw data.
From the docs:

// Are you expecting a massive dataset from the DB,
// and don't want to spend the time building DAOs for each entry?
// You can pass an extra query option to get the raw data instead:
Project.findAll({ where: { ... }, raw: true })

[uncedric]

I know! I have the same problem :(

[vlad-grybennikov]

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

[holmberd]

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.

[pateketrueke]

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!

[janudewangga]

Removing raw:true from outer level worked for me

worked for me too...

[iamolegga]

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

[mahiraltinkaya]

Hey, i have same issue, have any fix?
i am using global raw:true;
Below code is working. But give me only first object not all objects. Any one help ?

  Users.findOne({
    where: { email: req.body.email, password: req.body.password },
    raw: true,
    nest: true,
    include: {
      attributes: {
        exclude: ["id"],
      },
      model: Payments,
      require: true,
    },
  })```

[trig79]

The solution that worked for me:
I do not have 'id' in my 'Models' , MySQL does actually have them as standard in the tables with auto increment but I rarely need to reference them as I store UUID and other unique data. The solutions that worked for me was to not assign a primaryKey in the 'Model's' instead when building association's I always write with 'foreignKey and sourceKey' as example:

 this.hasMany(Document, {
          foreignKey: 'orderNumber',
          sourceKey: 'orderNumber',
        })

Full disclosure: I'm not the most experienced Dev, nor do i work on large complex applications but hey this may help someone.

Cheers

[mahiraltinkaya]

The solution that worked for me:
I do not have 'id' in my 'Models' , MySQL does actually have them as standard in the tables with auto increment but I rarely need to reference them as I store UUID and other unique data. The solutions that worked for me was to not assign a primaryKey in the 'Model's' instead when building association's I always write with 'foreignKey and sourceKey' as example:

 this.hasMany(Document, {
          foreignKey: 'orderNumber',
          sourceKey: 'orderNumber',
        })

Full disclosure: I'm not the most experienced Dev, nor do i work on large complex applications but hey this may help someone.

Cheers

Hey bro i tried, but after added sourceKey gived me 'UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'field' of undefined' this error.

Users.hasMany(Payments, { foreignKey: "user_id", sourceKey: "user_id" });
  Payments.belongsTo(Users, { foreignKey: "id" });
  Users.findOne({
    where: { email: req.body.email, password: req.body.password },
    raw: true,
    nest: true,
    include: {
      attributes: {
        exclude: ["id"],
      },
      model: Payments,
      require: true,
      all: true,
    },
  }),

[trig79]

Users.hasMany(Payments, { foreignKey: "user_id", sourceKey: "user_id" });
  Payments.belongsTo(Users, { foreignKey: "id" });

i had this error and it led to one of my models not having that field in it.

do you have 'id' in your User Model?
do you have 'user_id' in your User Model?
do you have 'id' in your Payment Model?
do you have 'user_id' in your Payment Model?

the sourceKey references User the User Model

like i say i haven't got lots of experience but maybe this helps?

[mahiraltinkaya]

Users and Payments model has id also Payments model has user_id

[trig79]

Hey mate

so the way I see it is you need to try

User hasMany Payments : change sourceKey to ‘id’.
The sourceKey is referencing User, so if you don’t have user_id on the User Model then that is where the error is probably coming from.

[mahiraltinkaya]

Hey mate thank you for help;
Shared below complately. I think you can help me.

After edit;

SELECT `Users`.*, `Payments`.`user_id` AS `Payments.user_id`, `Payments`.`package` AS `Payments.package`, `Payments`.`price` AS `Payments.price`, `Payments`.`trtotal` AS `Payments.trtotal`, `Payments`.`dolar_kur` AS `Payments.dolar_kur`, `Payments`.`point` AS `Payments.point`, `Payments`.`type` AS `Payments.type`, `Payments`.`update` AS `Payments.update`, `Payments`.`status` AS `Payments.status`, `Payments`.`order_id` AS `Payments.order_id`, `Payments`.`createdAt` AS `Payments.createdAt`, `Payments`.`updatedAt` AS `Payments.updatedAt` FROM (SELECT `Users`.`id`, `Users`.`username`, `Users`.`password`, `Users`.`name`, `Users`.`email`, `Users`.`phone`, `Users`.`country`, `Users`.`city`, `Users`.`address`, `Users`.`type`, `Users`.`reference`, `Users`.`sponsor`, `Users`.`parent`, `Users`.`direction`, `Users`.`career`, `Users`.`status`, 
`Users`.`createdAt`, `Users`.`updatedAt` FROM `Users` AS `Users` WHERE `Users`.`email` = 'xx@gmail.com' AND `Users`.`password` = 'xx' LIMIT 1) AS `Users` LEFT OUTER JOIN `Payments` AS `Payments` ON `Users`.`id` = `Payments`.`user_id`;

[KarenOk]

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" })

[uharston]

So has anyone really found a fix to this problem? This thread evolved into two different issues. The second issue having to do with raw: true.

I am having the original problem from the opening comment. Our MSSQL db doesn't want Sequelize to Insert_Identity so the Primary Key is disabled to allow creation coupled with Model.removeAttribute('id'). On Read queries, hasMany loads only one record. @lekhnath commented on setting up a composite unique key, but I haven't had success with that either. Any thoughts left out there?

[uharston]

Additionally I would like to add an example:

Foo.hasMany(bar)
Bar.belongsTo(foo)

Foo does not need the PK declared for it to work properly. Once you declare the PK on Bar, then it will fetch all of the associations.

Funny thing is that the SQL generated by both setups is exactly the same and they both retrieve all the association when inputted directly into SQL Server console. It seems that Sequelize configurations is blocking the rendering of all the associations.

[maeriens]

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.

[uharston]

This is great stuff right here.

Our problem specifically is that we cannot declare primaryKeys at all with our ORM because SQL Server does not want the ORM to Insert_Identity. So we are creating associations with out declaring the primary key. Ex:

`@Table
export class Foo {

@column({primaryKey: false})
idFoo: number

@hasmany(() => Bar, {
sourceKey: 'idFoo',
})
Bars: Bar[];
}

@table
export class Bar {

@column({primaryKey: false})
idBar: number

@ForiegnKey( () => Foo, )
idFoo: number

@BelongsTo(() => Foo, {
targetKey: 'idFoo'
})
foo: Foo;
}

`
As soon as I turn primaryKey to true on Foo, I get all of my records rather that one when calling something like foo.getBars().

https://sequelize.org/master/manual/assocs.html#creating-associations-referencing-a-field-which-is-not-the-primary-key

[osvaldokalvaitir]

Still no fix?!

[willy2dg]

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 ??

[ephys]

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

[Nziranziza]

Student.findAll({
  limit: 10,
  offset: 0,
  where: {
     '$or':  [
      { '$enrollments.studyGroupId$':  3 }
    ]
  },
  include: [
    {
      model: Enrollment,
      attributes: ['id'],
      as: 'enrollments',
      include: [StudyGroup]
    }
  ]
  subQuery: false,
})

I am running the above query and this is the result

What I expected to be returned

N.B: I did not set raw: true

[Sitronik]

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 ??

I have exactly the same problem, is there already a solution so that it can be used with raw: true ?

[RedSpid3r]

Ran into this issue today despite having a Primary Key.

The problem for me was my PK was 2 columns. As a workaround i added

            id: {
                type: DataTypes.BIGINT.UNSIGNED,
                autoIncrement: true,
                primaryKey: true,
            },

to my associated model and removed the original 2 columns from PK, now all results are returned correctly.

Would be great if this issue could be resolved (and also support PKs that consist of more than a single column)

[NikhilNickelfox]

Use :-
seperate:true

[zeid0ne]

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

I had the same problem in v 6.37.1 and that fixed it