CODEWITHSUNDEEP
javascriptnode.jsadonis.js
John Carty
John Carty

Reputation: 262

Join table on itself in Adonis.js Query Builder

I have a mssql table that stores employee information include the key for their manager. In sql this is easy enough to join on itself and get the manager's name with the employee record. I am trying to find a way to do this in adonis using the lucid query builder. The code I have currently is

const employees = await Database.from('Employees').orderBy('EmployeeName', 'asc')

Adonis has an innerJoin method but since the tables are the same I am not sure how to differentiate them in the join.

.innerJoin('Employees', 'Employees.id', 'Employees.manager_id')

so the full code would be something like

const employees = await Database.from('Employees')
.innerJoin('Employees', 'Employees.id', 'Employees.manager_id')
.orderBy('EmployeeName', 'asc')

This could be done easily just running raw sql in the app, but I would like to avoid that if at all possible.

Upvotes: 0

Views: 4973

Answers (1)

John Carty
John Carty

Reputation: 262

Figured it out. 


const employees = await Database.from('Employees as a')
.innerJoin('Employees as b', 'a.id', 'b.manager_id')
.orderBy('EmployeeName', 'asc')

Upvotes: 1

Related Questions