INNER JOIN Query with Objection JS?

Question

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)

?

Rashomon · Accepted Answer · 2022-03-23 07:59:18Z

14

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)

edited Mar 23, 2022 at 7:59

answered Feb 3, 2019 at 17:52

Rashomon

6,8924 gold badges40 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

erikvm Over a year ago

Interesting approach, didn't know you could do that type of inner joins with Objection. I'm using GraphQL for the queries though, so that messes things up, but thanks for the tip. Good to know

Rashomon Over a year ago

Objection.js has interesting Graph features. I would recommend you to check them vincit.github.io/objection.js/#graph-inserts :)

erikvm Over a year ago

Wow, Objection JS never fails to amaze me - what a terrific ORM

Brad Over a year ago

Thanks for the excellent example. I couldn't figure out why I kept getting "unknown relation". It turned out that I have to name my relations in the Model. +1!

erikvm · Accepted Answer · 2019-02-03 17:19:44Z

3

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')

answered Feb 3, 2019 at 17:19

erikvm

9581 gold badge12 silver badges37 bronze badges

1 Comment

jboxxx Over a year ago

still working in objection 3.0.x and knex 2.1.x. Thank you

Collectives™ on Stack Overflow

INNER JOIN Query with Objection JS?

2 Answers 2

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Related