CODEWITHSUNDEEP
node.jsdatabaseormsequelize.js
jarodsmk
jarodsmk

Reputation: 2089

Sequelize 'Inverse' Relationship

Not sure if I'm just being silly, but encountered something that's stumped me with regards to Sequelize.

Preamble

Let's say you have Users and Projects like the examples the Sequelize site provides, to keep things simple.

With a n:m relationship for this example, one User can be assigned to multiple Projects, and visa versa.

With relationships correctly set up, you should be able to call the following:

//To get the projects a user is assigned to:
user.getProjects();

//To get the users assigned to a project:
project.getUsers();

The Issue

While this is pretty handy already, how to I access the 'inverse' relationship, for instance Users NOT assigned to a Project, or Projects a User IS NOT a part of?

Would this be achieved via a scope, or can it be achieved with the creation of another relationship?

Kind regards :)

Upvotes: 1

Views: 648

Answers (1)

piotrbienias
piotrbienias

Reputation: 7401

There is couple of ways that can achieve what you are asking for. First one is to use getProjects() or getUsers() method to return objects assigned to User/Project and use it in order to return the unassigned records (something like id NOT IN (...))

user.getProjects().then(projects => {
    let mappedProjects = projects.map(project => {
        return project.id;
    });

    Project.all({
        where: { id: { $notIn: mappedProjects } }
    }).then(projects => {
        // projects not assigned to 'user'
    });
});

On the other hand, you can create some instance method like getUnassignedProjects() on User model that would perform the action above or similar with use of sequelize.literal(), sequelize.where() and sequelize.col() methods

instanceMethods: {
    getUnassignedProjects: function(){
        return sequelize.models.Project.all({
            where: sequelize.where(
                sequelize.col('Project.id'),
                ' NOT IN ',
                sequelize.literal(`(SELECT "projectId" FROM users_projects WHERE "userId" = ${this.id})`)
            )
        });
    }
}

Code above would generate query similar to this one

SELECT "id", "name" FROM "projects" AS "Project" WHERE "Project"."id" NOT IN (SELECT "project" FROM users_projects WHERE "userId" = 1);

The users_projects is of course the table connecting users and projects in n:m relation.

Upvotes: 1

Related Questions