admin管理员组文章数量:1332336
I am having a problem with grouping by associated models that are using aliases. The table on the client side has grouping, ordering, pivoting etc. Most of it I have down, but the grouping is causing me headaches.
"sequelize": "^6.37.3", node v16.15.1 (Using typescript but simplified for this example) My models: Booking BookingContainer Organisation
bookingContainer.associate = function (models) {
bookingContainer.belongsTo(models.booking)
}
booking.associate = function (models) {
booking.hasMany(models.bookingContainer)
booking.belongsTo(modelsanisation, {as: 'consignee'})
booking.belongsTo(modelsanisation, {as: 'shipper'})
booking.belongsTo(modelsanisation, {as: 'buyer'})
booking.belongsTo(modelsanisation, {as: 'customer'})
booking.belongsTo(modelsanisation, {as: 'shippingLine'})
booking.belongsTo(modelsanisation, {as: 'forwarder'})
}
We have an anisations table containing standardised info, with the 'roles' of the anisations on a booking being associated with aliases.Due to the specifics of the query, I need to do the query via the bookingContainer -> booking.
let query = {
where: {///},
limit,
offset,
attributes: ['id', ///],
include: [
{
model: booking,
required: true,
where: {///},
attributes: ['id', ///],
include: [
{
model: anisation,
as: 'shipper',
attributes: ['id', 'name' ///]
},
{
model: anisation,
as: 'consignee',
attributes: ['id', 'name' ///]
},
{
model: anisation,
as: 'buyer',
attributes: ['id', 'name' ///]
},
///etc.
]
}
]
}
//other query construct code
const groupResult = await bookingContainer.findAll(query);
However, when I try to add a group, I run into a whole bunch of problems.
query.group = [
[
{ model: booking },
{ model: anisation, as: 'shipper' },
'name'
]
];
// ❌ Error: Unknown structure passed to order / group: booking
Based on the docs, Sequelize - Model Querying | Grouping, the grouping structure is supposed to be based on the order structure, which the same nested structures seem to work for me.
The syntax for grouping and ordering are equal, except that grouping does not accept a direction as last argument of the array (there is no ASC, DESC, NULLS FIRST, etc).
My order code works for all of these columns. Here it is as a reference.
if (sortModel.length > 0) {
query.order = sortModel.map((sort: any) => {
const parts = sort.colId.split('.');
if (parts.length === 1) {
return [sort.colId, sort.sort.toUpperCase()];
}
if (parts.length > 1) {
let mappedParts = [...parts];
if(sort.colId.split('.').some((part: any) => Object.keys(modelMapping).includes(part))) {
for (let i = 0; i < parts.length; i++) {
if (modelMapping[parts[i]]) {
mappedParts[i] = modelMapping[parts[i]];
}
}
} else {
return [
Sequelize.literal(`\`${sort.colId}\``),
sort.sort.toUpperCase()
];
}
const associationPath = parts.slice(0, -1).map((part: any, index: any) => ({
model: eval(mappedParts[index]),
as: part
}));
return [
...associationPath,
parts[parts.length - 1],
sort.sort.toUpperCase()
];
}
}).filter(Boolean);
}
I've tried several different ways. Ive added the error logs below each attempt:
query.group = [
[
Sequelize.literal(`\`${'booking.shipper'}\``),
'name'
]
]
❌ Error: Unknown column 'booking.shipper' in 'group statement'
query.group = [
[
Sequelize.literal(`\`${'booking.associations.shipper'}\``),
'name'
]
]
❌ Error: Unknown column 'booking.associations.shipper' in 'group statement'
query.group = [
[
Sequelize.col('booking.shipper'),
'name'
]
]
❌ Error: Unknown column 'booking.shipper' in 'group statement'
query.group = [
[
'booking',
{
model: anisation,
as: 'shipper',
},
'name'
]
]
❌ Error: Unknown column 'booking' in 'group statement'
query.group = [
[
booking.associations.shipper,
'name'
]
]
❌ Error: Unknown structure passed to order / group: shipper
query.group = [
[
{ model: anisation, as: 'shipper' },
'name'
]
];
❌ Error: Unknown structure passed to order / group: { model: anisation, as: 'shipper' }
query.group = [
[
bookingContainer.associations.booking,
booking.associations.shipper,
'name'
]
]
❌ Error: Unknown structure passed to order / group: booking
query.group = [
[
{ model: booking },
{ model: anisation, as: 'shipper' },
'name'
]
];
❌ Error: Unknown structure passed to order / group: booking
query.group = [
[
{ model: bookingContainer },
{ model: booking },
{ model: anisation, as: 'shipper' },
'name'
]
];
❌ Error: Unknown structure passed to order / group: { model: bookingContainer }
query.group = [
[
Sequelize.col('booking->shipper.name'),
'name'
]
];
❌ Error: Unknown column 'booking->shipper.name' in 'group statement'
query.group = [
[
Sequelize.literal('`booking->shipper`.`name`')
]
];
❌ Error: Unknown column 'booking->shipper.name' in 'group statement'
query.group = [
[
Sequelize.literal('`bookings->anisations_shipper`.`name`')
]
];
❌ Error: Unknown column 'bookings->anisations_shipper.name' in 'group statement'
query.group = [
[
Sequelize.col('bookinganisation_shipper.name')
]
];
❌ Error: Unknown column 'booking->anisation_shipper.name' in 'group statement'
query.group = [
[
booking.associations.shipper,
'name'
]
]
❌ Error: Unknown structure passed to order / group: shipper
The query.group is to be constructed based on the user input, so these are test examples of what's expected from columns using aliases.
I am having a problem with grouping by associated models that are using aliases. The table on the client side has grouping, ordering, pivoting etc. Most of it I have down, but the grouping is causing me headaches.
"sequelize": "^6.37.3", node v16.15.1 (Using typescript but simplified for this example) My models: Booking BookingContainer Organisation
bookingContainer.associate = function (models) {
bookingContainer.belongsTo(models.booking)
}
booking.associate = function (models) {
booking.hasMany(models.bookingContainer)
booking.belongsTo(models.anisation, {as: 'consignee'})
booking.belongsTo(models.anisation, {as: 'shipper'})
booking.belongsTo(models.anisation, {as: 'buyer'})
booking.belongsTo(models.anisation, {as: 'customer'})
booking.belongsTo(models.anisation, {as: 'shippingLine'})
booking.belongsTo(models.anisation, {as: 'forwarder'})
}
We have an anisations table containing standardised info, with the 'roles' of the anisations on a booking being associated with aliases.Due to the specifics of the query, I need to do the query via the bookingContainer -> booking.
let query = {
where: {///},
limit,
offset,
attributes: ['id', ///],
include: [
{
model: booking,
required: true,
where: {///},
attributes: ['id', ///],
include: [
{
model: anisation,
as: 'shipper',
attributes: ['id', 'name' ///]
},
{
model: anisation,
as: 'consignee',
attributes: ['id', 'name' ///]
},
{
model: anisation,
as: 'buyer',
attributes: ['id', 'name' ///]
},
///etc.
]
}
]
}
//other query construct code
const groupResult = await bookingContainer.findAll(query);
However, when I try to add a group, I run into a whole bunch of problems.
query.group = [
[
{ model: booking },
{ model: anisation, as: 'shipper' },
'name'
]
];
// ❌ Error: Unknown structure passed to order / group: booking
Based on the docs, Sequelize - Model Querying | Grouping, the grouping structure is supposed to be based on the order structure, which the same nested structures seem to work for me.
The syntax for grouping and ordering are equal, except that grouping does not accept a direction as last argument of the array (there is no ASC, DESC, NULLS FIRST, etc).
My order code works for all of these columns. Here it is as a reference.
if (sortModel.length > 0) {
query.order = sortModel.map((sort: any) => {
const parts = sort.colId.split('.');
if (parts.length === 1) {
return [sort.colId, sort.sort.toUpperCase()];
}
if (parts.length > 1) {
let mappedParts = [...parts];
if(sort.colId.split('.').some((part: any) => Object.keys(modelMapping).includes(part))) {
for (let i = 0; i < parts.length; i++) {
if (modelMapping[parts[i]]) {
mappedParts[i] = modelMapping[parts[i]];
}
}
} else {
return [
Sequelize.literal(`\`${sort.colId}\``),
sort.sort.toUpperCase()
];
}
const associationPath = parts.slice(0, -1).map((part: any, index: any) => ({
model: eval(mappedParts[index]),
as: part
}));
return [
...associationPath,
parts[parts.length - 1],
sort.sort.toUpperCase()
];
}
}).filter(Boolean);
}
I've tried several different ways. Ive added the error logs below each attempt:
query.group = [
[
Sequelize.literal(`\`${'booking.shipper'}\``),
'name'
]
]
❌ Error: Unknown column 'booking.shipper' in 'group statement'
query.group = [
[
Sequelize.literal(`\`${'booking.associations.shipper'}\``),
'name'
]
]
❌ Error: Unknown column 'booking.associations.shipper' in 'group statement'
query.group = [
[
Sequelize.col('booking.shipper'),
'name'
]
]
❌ Error: Unknown column 'booking.shipper' in 'group statement'
query.group = [
[
'booking',
{
model: anisation,
as: 'shipper',
},
'name'
]
]
❌ Error: Unknown column 'booking' in 'group statement'
query.group = [
[
booking.associations.shipper,
'name'
]
]
❌ Error: Unknown structure passed to order / group: shipper
query.group = [
[
{ model: anisation, as: 'shipper' },
'name'
]
];
❌ Error: Unknown structure passed to order / group: { model: anisation, as: 'shipper' }
query.group = [
[
bookingContainer.associations.booking,
booking.associations.shipper,
'name'
]
]
❌ Error: Unknown structure passed to order / group: booking
query.group = [
[
{ model: booking },
{ model: anisation, as: 'shipper' },
'name'
]
];
❌ Error: Unknown structure passed to order / group: booking
query.group = [
[
{ model: bookingContainer },
{ model: booking },
{ model: anisation, as: 'shipper' },
'name'
]
];
❌ Error: Unknown structure passed to order / group: { model: bookingContainer }
query.group = [
[
Sequelize.col('booking->shipper.name'),
'name'
]
];
❌ Error: Unknown column 'booking->shipper.name' in 'group statement'
query.group = [
[
Sequelize.literal('`booking->shipper`.`name`')
]
];
❌ Error: Unknown column 'booking->shipper.name' in 'group statement'
query.group = [
[
Sequelize.literal('`bookings->anisations_shipper`.`name`')
]
];
❌ Error: Unknown column 'bookings->anisations_shipper.name' in 'group statement'
query.group = [
[
Sequelize.col('booking.anisation_shipper.name')
]
];
❌ Error: Unknown column 'booking->anisation_shipper.name' in 'group statement'
query.group = [
[
booking.associations.shipper,
'name'
]
]
❌ Error: Unknown structure passed to order / group: shipper
The query.group is to be constructed based on the user input, so these are test examples of what's expected from columns using aliases.
Share Improve this question asked Nov 20, 2024 at 19:27 ZarasmusZarasmus 12 bronze badges 2 |1 Answer
Reset to default 0Posting in case anyone ends up having the same issue in the future. I figured out the issue and have a temporary solution for it.
What's happening is that Sequelize is adding the GROUP BY statement before the LEFT OUTER JOIN statement in the generated SQL.
SELECT
`bookingContainer`.*,
`booking->shipper`.`id` AS `booking.shipper.id`,
`booking->shipper`.`name` AS `booking.shipper.name`,
`booking->buyer`.`id` AS `booking.buyer.id`,
`booking->buyer`.`name` AS `booking.buyer.name`,
`booking->consignee`.`id` AS `booking.consignee.id`,
`booking->consignee`.`name` AS `booking.consignee.name`,
FROM
(
SELECT
`bookingContainer`.`id`,
COALESCE(`booking->consignee`.`name`, 'NULL') AS `groupKey`, <--- This is an issue
COUNT(`bookingContainer`.`id`) AS `count`,
`booking`.`id` AS `booking.id`,
`booking`.`shipperId` AS `booking.shipperId`,
`booking`.`forwarderId` AS `booking.forwarderId`,
`booking`.`buyerId` AS `booking.buyerId`,
`booking`.`consigneeId` AS `booking.consigneeId`,
FROM
`bookingContainers` AS `bookingContainer`
INNER JOIN `bookings` AS `booking` ON `bookingContainer`.`bookingId` = `booking`.`id`
GROUP BY
COALESCE(`booking->consignee`.`name`, 'NULL') <--- Group statement before alias defined
ORDER BY
`bookingContainer`.`status` DESC
LIMIT
0, 100
) AS `bookingContainer`
LEFT OUTER JOIN `anisations` AS `booking->shipper` ON `booking.shipperId` = `booking->shipper`.`id`
LEFT OUTER JOIN `anisations` AS `booking->buyer` ON `booking.buyerId` = `booking->buyer`.`id`
LEFT OUTER JOIN `anisations` AS `booking->consignee` ON `booking.consigneeId` = `booking->consignee`.`id` <---Include alias defined here
ORDER BY
`bookingContainer`.`status` DESC;
So the GROUP BY is trying to group by an alias that hasnt yet been defined.
Thus you get the error: 'Unknown column booking->shipper in 'group statement''.
In order to get the GROUP BY statement to run after the LEFT OUTER JOIN has been defined, you'll need to run it with subquery = false. (There may be a better solution out there but this is what I found.)
Here's what I ended up doing for my problem:
const currentGroupField = 'booking.shipper.name'
const currentParts = currentGroupField.split('.');
if(currentParts.length === 1) {
// For simple fields directly on bookingContainer
query.attributes = [
...query.attributes,
[Sequelize.literal(`IFNULL(\`bookingContainer\`.\`${currentGroupField}\`, 'NULL')`), 'groupKey'],
[Sequelize.fn('COUNT', Sequelize.col('bookingContainer.id')), 'count']
];
query.group = [[Sequelize.literal(`IFNULL(\`bookingContainer\`.\`${currentGroupField}\`, 'NULL')`)]];
} else {
const parts = currentParts;
const buildModelPath = (parts: string[]) => {
if (parts[0] === 'booking') {
const fieldName = parts[parts.length - 1];
const associationPath = parts.slice(0, -1).join('->');
return `COALESCE(\`${associationPath}\`.\`${fieldName}\`, 'NULL')`;
}
return `COALESCE(\`${parts.join('.')}\`, 'NULL')`;
};
const fullPath = buildModelPath(parts);
// Remove existing attributes and replace with grouped ones
query.attributes = [
[Sequelize.literal(fullPath), 'groupKey'],
[Sequelize.fn('COUNT', Sequelize.col('bookingContainer.id')), 'count']
];
query.group = [Sequelize.literal(fullPath)];
// Keep raw and subQuery settings
query.raw = true;
query.subQuery = false;
I need my grouping to be dynamic based on whats passed as the currentGroupField, so this is very much specific to my own needs. I'm in no way an expert at Sequelize, and this solution is specific to my own needs, but if it proves helpful to anyone, then great.
本文标签： javascriptSequelize Grouping by Associations with aliases (modelgtmodel2 AS 39alias39)Stack Overflow
版权声明：本文标题：javascript - Sequelize Grouping by Associations with aliases (model->model2 AS 'alias') - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1742334846a2455419.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
Sequelize.col('booking.shipper.name')? – Anatoly Commented Nov 20, 2024 at 20:04