For a query which returns "parts" with collections of various child records, Sequelize is mostly doing what I want. The result includes the desired child record collections. However, when the user wants to perform a search -- which adds a WHERE clause with references to the child records -- Sequelize appears to forget that it needs to include various JOINs in the subquery it generates to filter the results.
Should it matter, the RDBMS is PostgreSQL.
Everything is working except when the user wants to perform a search, triggering a WHERE clause.
SELECT "parts".*
, "part_catalog"."id" AS "part_catalog.id"
, "part_catalog"."name" AS "part_catalog.name"
, "part_catalog"."created_at" AS "part_catalog.createdAt"
, "part_catalog"."updated_at" AS "part_catalog.updatedAt"
, "part_catalog"."deleted_at" AS "part_catalog.deletedAt"
, "vendor"."id" AS "vendor.id"
, "vendor"."name" AS "vendor.name"
, "recounted_user"."id" AS "recounted_user.id"
, "recounted_user"."name" AS "recounted_user.name"
, "recounted_user"."email" AS "recounted_user.email"
, "child_assemblies"."part_id_parent" AS "child_assemblies.part_id_parent"
, "child_assemblies"."part_id_child" AS "child_assemblies.part_id_child"
, "child_assemblies"."quantity" AS "child_assemblies.quantity"
, "child_assemblies"."created_at" AS "child_assemblies.createdAt"
, "child_assemblies"."updated_at" AS "child_assemblies.updatedAt"
, "parent_assemblies"."part_id_parent" AS "parent_assemblies.part_id_parent"
, "parent_assemblies"."part_id_child" AS "parent_assemblies.part_id_child"
, "parent_assemblies"."quantity" AS "parent_assemblies.quantity"
, "parent_assemblies"."created_at" AS "parent_assemblies.createdAt"
, "parent_assemblies"."updated_at" AS "parent_assemblies.updatedAt"
, "part_orders"."id" AS "part_orders.id"
, "part_orders"."requester_id" AS "part_orders.requester_id"
, "part_orders"."handler_id" AS "part_orders.handler_id"
, "part_orders"."priority" AS "part_orders.priority"
, "part_orders"."status" AS "part_orders.status"
, "part_orders"."part_id" AS "part_orders.part_id"
, "part_orders"."vendor_id" AS "part_orders.vendor_id"
, "part_orders"."price_per" AS "part_orders.price_per"
, "part_orders"."quantity" AS "part_orders.quantity"
, "part_orders"."quantity_received" AS "part_orders.quantity_received"
, "part_orders"."po_number" AS "part_orders.po_number"
, "part_orders"."created_at" AS "part_orders.createdAt"
, "part_orders"."updated_at" AS "part_orders.updatedAt" FROM (
SELECT "parts"."id"
, "parts"."part_catalog_id"
, "parts"."vendor_id"
, "parts"."inventory_number"
, "parts"."description"
, "parts"."location"
, "parts"."on_hand"
, "parts"."min_threshold"
, "parts"."max_threshold"
, "parts"."recount_interval"
, "parts"."recounted_at"
, "parts"."recounted_by"
, "parts"."created_at" AS "createdAt"
, "parts"."updated_at" AS "updatedAt"
, "parts"."deleted_at" AS "deletedAt"
FROM "parts" AS "parts"
LEFT OUTER JOIN "part_catalogs" AS "part_catalog" ON "parts"."part_catalog_id" = "part_catalog"."id" AND ("part_catalog"."deleted_at" IS NULL)
LEFT OUTER JOIN "vendors" AS "vendor" ON "parts"."vendor_id" = "vendor"."id" AND ("vendor"."deleted_at" IS NULL)
LEFT OUTER JOIN "users" AS "recounted_user" ON "parts"."recounted_by" = "recounted_user"."id"
WHERE (
"parts"."deleted_at" IS NULL
AND (
"part_catalog"."name" ILIKE '%assembly%'
OR "vendor"."name" ILIKE '%assembly%'
OR "parts"."inventory_number" ILIKE '%assembly%'
OR "parts"."description" ILIKE '%assembly%'
OR "parts"."location" ILIKE '%assembly%'
OR "recounted_user"."name" ILIKE '%assembly%'
OR "recounted_user"."email" ILIKE '%assembly%'
)
)
ORDER BY "inventory_number" ASC
LIMIT 25
OFFSET 0
) AS "parts"
LEFT OUTER JOIN "part_catalogs" AS "part_catalog" ON "parts"."part_catalog_id" = "part_catalog"."id" AND ("part_catalog"."deleted_at" IS NULL)
LEFT OUTER JOIN "vendors" AS "vendor" ON "parts"."vendor_id" = "vendor"."id" AND ("vendor"."deleted_at" IS NULL)
LEFT OUTER JOIN "users" AS "recounted_user" ON "parts"."recounted_by" = "recounted_user"."id"
LEFT OUTER JOIN "assemblies" AS "child_assemblies" ON "parts"."id" = "child_assemblies"."part_id_parent"
LEFT OUTER JOIN "assemblies" AS "parent_assemblies" ON "parts"."id" = "parent_assemblies"."part_id_child"
LEFT OUTER JOIN "part_orders" AS "part_orders" ON "parts"."id" = "part_orders"."part_id" AND "part_orders"."status" NOT IN ('RECEIVED', 'CLOSED-CANCELLED', 'CLOSED-REJECTED', 'CLOSED-COMPLETE')
ORDER BY "inventory_number" ASC
;
SELECT "parts".*
, "part_catalog"."id" AS "part_catalog.id"
, "part_catalog"."name" AS "part_catalog.name"
, "part_catalog"."created_at" AS "part_catalog.createdAt"
, "part_catalog"."updated_at" AS "part_catalog.updatedAt"
, "part_catalog"."deleted_at" AS "part_catalog.deletedAt"
, "vendor"."id" AS "vendor.id"
, "vendor"."name" AS "vendor.name"
, "recounted_user"."id" AS "recounted_user.id"
, "recounted_user"."name" AS "recounted_user.name"
, "recounted_user"."email" AS "recounted_user.email"
, "child_assemblies"."part_id_parent" AS "child_assemblies.part_id_parent"
, "child_assemblies"."part_id_child" AS "child_assemblies.part_id_child"
, "child_assemblies"."quantity" AS "child_assemblies.quantity"
, "child_assemblies"."created_at" AS "child_assemblies.createdAt"
, "child_assemblies"."updated_at" AS "child_assemblies.updatedAt"
, "parent_assemblies"."part_id_parent" AS "parent_assemblies.part_id_parent"
, "parent_assemblies"."part_id_child" AS "parent_assemblies.part_id_child"
, "parent_assemblies"."quantity" AS "parent_assemblies.quantity"
, "parent_assemblies"."created_at" AS "parent_assemblies.createdAt"
, "parent_assemblies"."updated_at" AS "parent_assemblies.updatedAt"
, "part_orders"."id" AS "part_orders.id"
, "part_orders"."requester_id" AS "part_orders.requester_id"
, "part_orders"."handler_id" AS "part_orders.handler_id"
, "part_orders"."priority" AS "part_orders.priority"
, "part_orders"."status" AS "part_orders.status"
, "part_orders"."part_id" AS "part_orders.part_id"
, "part_orders"."vendor_id" AS "part_orders.vendor_id"
, "part_orders"."price_per" AS "part_orders.price_per"
, "part_orders"."quantity" AS "part_orders.quantity"
, "part_orders"."quantity_received" AS "part_orders.quantity_received"
, "part_orders"."po_number" AS "part_orders.po_number"
, "part_orders"."created_at" AS "part_orders.createdAt"
, "part_orders"."updated_at" AS "part_orders.updatedAt" FROM (
SELECT "parts"."id"
, "parts"."part_catalog_id"
, "parts"."vendor_id"
, "parts"."inventory_number"
, "parts"."description"
, "parts"."location"
, "parts"."on_hand"
, "parts"."min_threshold"
, "parts"."max_threshold"
, "parts"."recount_interval"
, "parts"."recounted_at"
, "parts"."recounted_by"
, "parts"."created_at" AS "createdAt"
, "parts"."updated_at" AS "updatedAt"
, "parts"."deleted_at" AS "deletedAt"
FROM "parts" AS "parts" WHERE (
"parts"."deleted_at" IS NULL
AND (
"part_catalog"."name" ILIKE '%assembly%'
OR "vendor"."name" ILIKE '%assembly%'
OR "parts"."inventory_number" ILIKE '%assembly%'
OR "parts"."description" ILIKE '%assembly%'
OR "parts"."location" ILIKE '%assembly%'
OR "recounted_user"."name" ILIKE '%assembly%'
OR "recounted_user"."email" ILIKE '%assembly%'
)
)
ORDER BY "inventory_number" ASC
LIMIT 25
OFFSET 0
) AS "parts"
LEFT OUTER JOIN "part_catalogs" AS "part_catalog" ON "parts"."part_catalog_id" = "part_catalog"."id" AND ("part_catalog"."deleted_at" IS NULL)
LEFT OUTER JOIN "vendors" AS "vendor" ON "parts"."vendor_id" = "vendor"."id" AND ("vendor"."deleted_at" IS NULL)
LEFT OUTER JOIN "users" AS "recounted_user" ON "parts"."recounted_by" = "recounted_user"."id"
LEFT OUTER JOIN "assemblies" AS "child_assemblies" ON "parts"."id" = "child_assemblies"."part_id_parent"
LEFT OUTER JOIN "assemblies" AS "parent_assemblies" ON "parts"."id" = "parent_assemblies"."part_id_child"
LEFT OUTER JOIN "part_orders" AS "part_orders" ON "parts"."id" = "part_orders"."part_id" AND "part_orders"."status" NOT IN ('RECEIVED', 'CLOSED-CANCELLED', 'CLOSED-REJECTED', 'CLOSED-COMPLETE')
ORDER BY "inventory_number" ASC
;
You can see that Sequelize omits the three required LEFT OUTER JOINs within the FROM subquery. Without them, Sequelize encounters and emits an RDBMS error when the query is run:
error: missing FROM-clause entry for table "part_catalog"
The following are snippets from a larger enterprise application that I'm developing.
const baseInclude = [
{ model: part_catalogs
, as: 'part_catalog'
}
, { model: vendors
, attributes: ['name']
, as: 'vendor'
}
, { model: users
, as: 'recounted_user'
, attributes: ['id', 'name', 'email']
}
];
function getAllWithChildren(options) {
// This is akin to getAll except that it extends the baseInclude to include
// child assemblies, parent assemblies, and open part orders. This
// facilitates a dynamic user interface (UI) and simplified user experience
// (UX) for part management where related records are easily accessible to
// the UI code. Such accessibility gives the user a more complete view of
// the part without forcing the user to switch context by navigating to
// separate screens or views.
//
// This is a more expensive query than getAll and should be used sparingly,
// limited to administrative (part management) and drill-in reporting
// purposes.
const includeWithChildren = [
...baseInclude
, { model: assemblies
, as: 'child_assemblies'
}
, { model: assemblies
, as: 'parent_assemblies'
}
, { model: part_orders
, where: {
status: {
[Sequelize.Op.notIn]: [
'RECEIVED'
, 'CLOSED-CANCELLED'
, 'CLOSED-REJECTED'
, 'CLOSED-COMPLETE'
]
}
}
, required: false
}
];
const queryOptions = translateOptionsToQueryArgs(options); // Omitted here because you can see its output below
queryOptions.include = includeWithChildren;
return parts.findAll(queryOptions);
}
The actual code is naturally more complex than this. It performs a dynamic build of the Sequelize options based on user interactions with the UI. For brevity, I have reduced the rest of the code to the resulting options it builds for Sequelize. The model can be inferred from the queries, above.
The resulting Sequelize options, queryOptions:
{
where: {
[Symbol(or)]: [
{ '$part_catalog.name$': { [Symbol(iLike)]: Literal { val: ':find' } } },
{ '$vendor.name$': { [Symbol(iLike)]: Literal { val: ':find' } } },
{ inventory_number: { [Symbol(iLike)]: Literal { val: ':find' } } },
{ description: { [Symbol(iLike)]: Literal { val: ':find' } } },
{ location: { [Symbol(iLike)]: Literal { val: ':find' } } },
{ '$recounted_user.name$': { [Symbol(iLike)]: Literal { val: ':find' } } },
{ '$recounted_user.email$': { [Symbol(iLike)]: Literal { val: ':find' } } }
]
},
include: [
{ model: [Function], as: 'part_catalog' },
{ model: [Function], attributes: [ 'name' ], as: 'vendor' },
{ model: [Function], as: 'recounted_user', attributes: [ 'id', 'name', 'email' ] },
{ model: [Function], as: 'child_assemblies' },
{ model: [Function], as: 'parent_assemblies' },
{ model: [Function], where: { status: { [Symbol(notIn)]: ['RECEIVED', 'CLOSED-CANCELLED', 'CLOSED-REJECTED', 'CLOSED-COMPLETE'] } }, required: false }
],
order: [ [ Col { col: 'inventory_number' }, 'ASC' ] ],
limit: 25,
offset: 0,
replacements: { find: '%assembly%' }
}
What changes do I need to make to queryOptions in order to instruct Sequelize to employ the missing JOINs within the FROM subquery it generates?
I have attempted various combinations of required and duplicates to no avail. While the right combination resolves the RDBMS error by way of eliminating the FROM subquery, the resulting data-set is not what I need because the LIMIT gets applied incorrectly to the entire result (expanded with child records) rather than only the parent. This produces a heavily truncated view of the data.
For @Anatoly, these are the actual relationships in the model:
part_catalogs.hasMany(parts, { foreignKey: 'part_catalog_id' });
parts.belongsTo(part_catalogs, { foreignKey: 'part_catalog_id' });
vendors.hasMany(parts, { foreignKey: 'vendor_id' });
parts.belongsTo(vendors, { foreignKey: 'vendor_id' });
parts.hasMany(part_orders, { foreignKey: 'part_id' });
part_orders.belongsTo(parts, { foreignKey: 'part_id' });
parts.hasMany(assemblies, { foreignKey: 'part_id_parent', as: 'child_assemblies' });
assemblies.belongsTo(parts, { foreignKey: 'part_id_parent', as: 'parent' });
parts.hasMany(assemblies, { foreignKey: 'part_id_child', as: 'parent_assemblies' });
assemblies.belongsTo(parts, { foreignKey: 'part_id_child', as: 'child' });
You need to exclude all hasMany associations to be joined in the single SQL by indicating separate: true in each such include option.
If you have conditions in some belongsTo associations - it's ok, but you then need to indicate subQuery: false