#StackBounty: #node.js #sequelize.js Sequelize – Not quite getting the correct results from a many to many select

Bounty: 100

I’m fairly new to Sequelize, but not to SQL. But I’m really struggling with how to correctly get my results from a many to many relationship.

My Schema:
My Schema

So I need to get all the Sheets with a given vendor_id and a given chemical_id.

Here is my Sequelize that is close but not quite producing the right query.

GetVendorFiles: async function(args) {
            console.log('args: ', args);
            if (!args.vendor_id) {
                throw new Error('Invalid argument: vendor_id');
            }
            let chemical_id = '';
            let load_id = '';

            if (args.load) {
                // first get the load id
                load_id = await db.Load.findOne({
                    attributes: ['id'],
                    where: { value: args.load }
                }).then((r) => console.log('load id: ', r));
            } else if (args.chemical) {
                // first get the load id
                chemical_id = await db.Chemical.findOne({
                    attributes: ['id'],
                    where: { name: args.chemical }
                }).then((r) => {
                    return r.dataValues.id;
                });
            }

            return await db.Vendor.findOne({
                where: { id: args.vendor_id }
            }).then(async function(vendor) {
                // build the include block conditionally based on whether
                // a chemical or load was passed in
                const model =
                    load_id !== ''
                        ? db.Load
                        : chemical_id !== ''
                        ? db.Load
                        : db.Sheet;
                const includeBlock = load_id
                    ? [
                            {
                                model: db.Load,
                                as: 'loads',
                                where: { id: load_id }
                            }
                      ]
                    : chemical_id
                    ? [
                            {
                                model: db.Chemical,
                                as: 'chemicals',
                                where: { id: chemical_id }
                            }
                      ]
                    : [];
                const files = await db.Sheet.findAll({
                    attributes: ['sheet_name', 'sheet_file_name'],
                    include: includeBlock,
                    model: model,
                    where: {
                        deleted_at: null,
                        vendor_id: vendor.dataValues.id
                    }
                });
                if (files) {
                    return files;
                }
                return null;
            });
        },

And here is the query it is producing:

SELECT `Sheet`.`id`, `Sheet`.`sheet_name`, `Sheet`.`sheet_file_name`, `chemicals`.`id` AS `chemicals.id`, `chemicals`.`name` AS `chemicals.name`, `chemicals`.`created_at` AS `chemicals.created_at`, `chemicals`.`updated_at` AS `chemicals.updated_a
t`, `chemicals`.`deleted_at` AS `chemicals.deleted_at`, `chemicals`.`created_at` AS `chemicals.createdAt`, `chemicals`.`updated_at` AS `chemicals.updatedAt`, `chemicals`.`deleted_at` AS `chemicals.deletedAt`, `chemicals->chemicals_to_loads`.`created_at` AS `chemicals
.chemicals_to_loads.createdAt`, `chemicals->chemicals_to_loads`.`updated_at` AS `chemicals.chemicals_to_loads.updatedAt`, `chemicals->chemicals_to_loads`.`load_id` AS `chemicals.chemicals_to_loads.load_id`, `chemicals->chemicals_to_loads`.`chemical_id` AS `chemicals.
chemicals_to_loads.ChemicalId` FROM `sheets` AS `Sheet` INNER JOIN ( `chemicals_to_loads` AS `chemicals->chemicals_to_loads` INNER JOIN `chemicals` AS `chemicals` ON `chemicals`.`id` = `chemicals->chemicals_to_loads`.`chemical_id`) ON `Sheet`.`id` = `chemicals->chemi
cals_to_loads`.`load_id` AND (`chemicals`.`deleted_at` IS NULL AND `chemicals`.`id` = 24) WHERE (`Sheet`.`deleted_at` IS NULL AND (`Sheet`.`deleted_at` IS NULL AND `Sheet`.`vendor_id` = '157'));

Towards the end it says ON Sheet.id = chemicals->chemicals_to_loads.load_id and I learned I simply need to change that Sheet.id to Sheet.load_id and everything works perfect! But how do I get the Sequelize to do that?

Update: Here is some troubleshooting I have done to try and fix it but to no avail. I thought I might need to properly define the foreign key in the Sheets table. So in my index.js under models where I am doing a lot of my associations, I added the foreignKey option to this line:

// Sheets- foreign key in sheets table "belongs to" loads table
db.Sheet.belongsTo(db.Load, { foreignKey: 'load_id' });

Unfortunately, that did not seem to have any effect.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.