CODEWITHSUNDEEP
javascriptnode.jspostgresqlobjection.js
erikvm
erikvm

Reputation: 928

INNER JOIN Query with Objection JS?

I got the following data model: https://i.sstatic.net/g1T5i.jpg

Basically, A User can belong to many Projects, and a Project can have many Users and I'm tying that together through a join table called UserProjects

With a raw SQL query I can go 

SELECT "user".email, project.name FROM "user"
JOIN userprojects ON userprojects.user_id = "user".id
JOIN project ON project.id = userprojects.project_id

Which gives me 

email(On User table)               name(On Project table)
[email protected]                    Project X
[email protected]                    Project Y
second@email                       Project Y

How would I structure this query with Objection ORM? Perhaps I can just do a raw query straight? Something like

User.query().raw(SELECT "user".email, project.name FROM "user"
JOIN userprojects ON userprojects.user_id = "user".id
JOIN project ON project.id = userprojects.project_id)

?

Upvotes: 3

Views: 10713

Answers (2)

Rashomon
Rashomon

Reputation: 6762

instead of doing all the stuff by yourself, Objection.js can do it for you. You can just declare a ManyToManyRelation.

      static relationMappings = {
        projects: {
          relation: Model.ManyToManyRelation,
          modelClass: Project, // imported Objection class of "Project"
          join: {
            from: 'user.id',
            through: {
              from: 'userprojects.user_id',
              to: 'userprojects.project_id'
            },
            to: 'project.id'
          }
        }
      }

Then you can just get the projects of a User using eager loading:

    User.query().eager('projects').findById(userId)

And you will get something like:

    User {
      id: 3,
      firstname: 'firstname',
      lastname: 'lastname',
      email: 'email',
      projects: [
        {id: 1,
        name: 'name1'},
        {id: 2,
        name: 'name2'},
      ]
    }

2020 Update:

Since version 2 of Objection.js, eager method has been renamed as withGraphFetched:

    User.query().withGraphFetched('projects').findById(userId)

Upvotes: 14

erikvm
erikvm

Reputation: 928

Nvm, found a solution

/close

JK,

Here's what worked for me if anyone else would run into the same issue:

return User.query().where("user_id", parent.id)
.join('userprojects', 'user.id', '=', 'userprojects.user_id')
.join('project', 'project.id', '=', 'userprojects.project_id')
.select('user.id', 'userprojects.project_id', 'project.name')

Upvotes: 3

Related Questions