Q:

Sequelize: Using "where" inside a child include overrides parent's "where" criteria. Composite primary key involved

So,
I have two tables, with a 1:M relationship. They have in common two primary keys: tenant and user_id.
I have defined the model relationship and btw, I am not sure if I did it correctly because I am still not sure how to handle composite primary keys on Sequelize. This works well with my many other queries, and I think it influences the problem.
// Sequelize model set-up:
const user = serviceLayerDB.define('user',
    { // Database columns:
        tenant: {
            type: Sequelize.STRING(45),
            primaryKey: true
        },
        user_id: {
            type: Sequelize.STRING(24),
            primaryKey: true
        },
        status: {
            type: Sequelize.STRING(11)
        }
    });

const user_component = serviceLayerDB.define('user_component',
    { // Database columns:
        tenant: {
            type: Sequelize.STRING(45),
            primaryKey: true
        },
        user_id: {
            type: Sequelize.STRING(24),
            primaryKey: true
        },
        component_id: {
            type: Sequelize.STRING(24),
            primaryKey: true
        },
        active: {
            type: Sequelize.BOOLEAN
        }
    });

// Sequelize relationship set-up:
user.hasMany(user_component, { foreignKey: 'user_id' });
user.hasMany(user_component, { foreignKey: 'tenant' });

BUT the problem comes when I have the following query:
// Retrieving user and related components.
function getSubscriptions() {
        let options = {
            where: {
                tenant: 'company_A',
                user_id: '1001'
            },
            include: [{ // Adding components, filtered by "active" value:
                model: user_component,
                where: {
                    active: 1
                },
                required: false
            }]
        };

        user.findAll(options)
            .then(function(data) {
                if (data.length === 0) { // If no data found:
                    console.log('No data found');
                    return;
                }

                // Curate Sequelize result:
                let curatedData = data.map(function(userInstance) { return userInstance.get({ plain: true}) }); // Workaround to be able to perform .get().
                console.log(JSON.stringify(curatedData, null, 2));
            })
            .catch(function(error) {
                console.log('critical', 'Failed to find data in database. Error: ' + error);
            })
}

// Execute:
getSubscriptions();

What I want is to find the user and its components, but only the ones with the active value set to 1. It is not working: the result is every component with the value active set to 1 under the same "tenant", the child include is ignoring the "user_id" that we indicated in the parent.
Am I right to think this is related to my composite primary key? How to fix this in the most elegant manner?

A:

You have to use aliases when you associate a model to another model more then once.
For instance:
user.hasMany(user_component, { foreignKey: 'user_id', as: 'UserComponents' });
user.hasMany(user_component, { foreignKey: 'tenant', as: 'TenantComponents' });

And afterwards you should decide for what exact association you wish to do an include operation:
the association by user_id field
let options = {
            where: {
                tenant: 'company_A',
                user_id: '1001'
            },
            include: [{ // Adding components, filtered by "active" value:
                model: user_component,
                as: 'UserComponents'
                where: {
                    active: 1
                },
                required: false
            }]
        };

the association by tenant field
let options = {
            where: {
                tenant: 'company_A',
                user_id: '1001'
            },
            include: [{ // Adding components, filtered by "active" value:
                model: user_component,
                as: 'TenantComponents'
                where: {
                    active: 1
                },
                required: false
            }]
        };

If you would like both child collections with the active: 1 condition you can do this:
let options = {
            where: {
                tenant: 'company_A',
                user_id: '1001'
            },
            include: [{ // Adding components, filtered by "active" value:
                model: user_component,
                as: 'UserComponents'
                where: {
                    active: 1
                },
                required: false,
                separate: true
            }, { // Adding components, filtered by "active" value:
                model: user_component,
                as: 'TenantComponents'
                where: {
                    active: 1
                },
                required: false,
                separate: true
            }]
        };

Please pay attention to separate: true option: this option tells sequielize to do separate queries for childs.
If you wish to get not all users but those ones with active only components (which ones: through user_id or tenant field?) you should set required: true in include. But in this case don't include both associations with required: true. This leads to miltiplication of amount of records in the result SQL query and consumes much more memory.

