sequelize: Empty rows returning, when n:m assotiation and limit condition, but count is right
What are you doing?
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('postgres://postgres@localhost:5432/testdb', { logging: false });
const User = sequelize.define('User', {
id: {
type: DataTypes.UUID,
primaryKey: true,
allowNull: false,
defaultValue: sequelize.fn('gen_random_uuid')
},
login: {
type: DataTypes.STRING(255),
allowNull: false,
unique: false // just for demo
}
}, {
timestamps: true,
paranoid: false,
tableName: 'users',
freezeTableName: true,
underscored: true,
modelName: 'Users',
scopes: {
page(index = 1, limit = 10) {
let $return = {
offset: 0,
limit: 10
};
if (limit !== Infinity && !isNaN(index) && !isNaN(limit)) {
$return = {
offset: (index - 1) * limit,
limit
};
}
return $return;
}
}
});
const ParentChild = sequelize.define('ParentChild', {}, {
timestamps: false,
paranoid: false,
tableName: 'users_parent_child',
freezeTableName: true,
underscored: true,
modelName: 'ParentChild'
});
User.belongsToMany(User, {
hooks: false,
as: {
singular: 'Parent',
plural: 'Parents'
},
through: {
model: ParentChild,
unique: true
},
foreignKey: 'child_id',
otherKey: 'parent_id'
});
User.belongsToMany(User, {
hooks: false,
as: {
singular: 'Child',
plural: 'Children'
},
through: {
model: ParentChild,
unique: true
},
foreignKey: 'parent_id',
otherKey: 'child_id'
});
let someParent = '';
sequelize.query(`CREATE EXTENSION IF NOT EXISTS "pgcrypto"`)
.then(() => sequelize.sync({ force: true }))
.then(() => Promise.all([
User.bulkCreate([...Array(10).keys()].map(() => ({login: 'c'}))),
User.bulkCreate([...Array(10).keys()].map(() => ({login: 'p'})))
])) // generate parents and children
.then(() => {
return Promise.all([
User.findAll({where: {login: 'c'}}),
User.findAll({where: {login: 'p'}})
]);
}) // find them
.then(([children, parents]) => {
someParent = parents[0].id;
return Promise.all(children.map(
(child, i) => child.setParents([
parents[i],
parents[(i + 1) % 10]
])
));
}) // set every child two parents
.then(() => {
return Promise.all([
User.scope({method: ['page', 1, 5]})
.findAndCountAll({
where: {
login: 'c'
},
include: [
{
association: 'Parents',
required: true,
attributes: [],
where: {
id: someParent
}
}
]
}),
User.findAndCountAll({
where: {
login: 'c'
},
include: [
{
association: 'Parents',
required: true,
attributes: [],
where: {
id: someParent
}
}
]
})
]);
}) // try to find children who has parent with id "someParent" and count them
.then(res => console.log(JSON.stringify(res, null, 4)))
.finally(() => sequelize.close());
To Reproduce Steps to reproduce the behavior:
- Run the following
What do you expect to happen?
[ { count: 2, rows: [ [User], [User] ] },
{ count: 2, rows: [ [User], [User] ] } ]
What is actually happening?
[ { count: 2, rows: [] },
{ count: 2, rows: [ [User], [User] ] } ]
Environment
Dialect:
- mysql
- postgres
- sqlite
- mssql
- any Dialect library (pg) version: 7.12.1 Database version: Postgres 10 Sequelize version: 5.13.1 Node Version: 10.16.0 OS: Win7x64 Tested with latest release:
- No
- Yes
About this issue
- Original URL
- State: open
- Created 5 years ago
- Reactions: 1
- Comments: 27 (10 by maintainers)
This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the “stale” label. 🙂
Hi,
Thx @mjy78. It solves a major issue. We rename the primary key on our sequelize model and the
findAllresults was empty except when the optionrawwas settrue. After digging into sequelize, I found the cause was caused in some ways by the function_groupJoinDatawhich is, by the way, quite horrible to read.The rows injected into the function was order in this way :
Thanks to
_.sortBy(Object.keys(row), k => _.join(_.dropRight(_.split(k, '.')), '.'));This thread is quite old and no patch seem to be on the version
6.5.0🤔 .Is there already a PR to correct this scenario on the query generator or on the
_groupJoinDatafunction ?I’m seeing a similar symptom where count is correct, but rows are empty, when using limit/offset (also Postgres dialect). In my case I’ve enabled query logging, and can actually see the rows are being returned from the query in both cases (with and without limit/offset).
I’ve dug deeper and it looks like the call to AbstractQuery._groupJoinData() inside of handleSelectQuery() is wiping out the result set after the data is returned.
Looking closer at the data that is being fed into this function, when limit/offset is in use, it looks like the primary key of the parent model is included in each object twice, one with a key of “_id” (the field name as defined in the model definition) and then also as “installation_request_id” (which is the db column name for the pk field).
I’ll keep digging further into this function, but just mentioning it here in case it is of any consequence.
I tried a workaround that actually worked for me. I don’t think if it’s good for everybody.
And that’s by adding
subQuery: falseWhen the primary key’s database field and model property are named differently and both are returned in the query result, this breaks the _groupJoinData() function because the second occurance of this key/value in the rows object is out of order.
According to comments at the head of _groupJoinData() the function makes some assumptions about the input data…
In particular, the assumption “All fields for a level are grouped in the same set” appears to be violated.
While the root cause of this issue is that the query generator is returning the primary key field twice, it’s probably a good idea to harden the _groupJoinData() function to either assert that the input data meets the requirements or to try to handle the data as it is, rather than just assume it will be in a certain format. Developers working on query generation may not be aware of the assumptions the _groupJoinData() function is making. Also, even though it works, it just doesn’t feel right relying on the keys being in a certain order from a call to Object.keys(row).
We could change the _groupJoinData() function to ensure the “assumptions” are met by sorting the keys so that fields for the same level are grouped together and that keys for higher level fields are processed before children.
So changing from this…
to this…
appears to resolve the issue at least in the scenario where the primary key model property is named differently to the database field.
Any thoughts before I raise a PR?
Thx, I tried to do this SSCCE. I think this dont depend on dialect, because this bug with self multi association. It generate SQL that has multiple “AS” and db dont know which one need to use. SQL, that genered with “wrong” query:
but when I change some
it’s right, or, as I think, it must be something like