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.
发布者：admin，转转请注明出处：http://www.yc00.com/questions/1742334847a4424444.html
Sequelize.col('booking.shipper.name')? – Anatoly Commented Nov 20, 2024 at 20:04