Search code examples
sqlleft-joininner-joinknex.js

Alias a table in Knex

I have a SQL query that refers to the same table twice, and I need to alias the table to two separate aliases. I can't quite figure out how to compose this with Knex.

There's a 'Words' table and a 'Users' table. The Words table has two foreign keys, 'author_id' and 'winner_id', referencing the Users table's 'id' column.

Here's the SQL I'm trying to compose in Knex:

SELECT w.*, ua.name, uw.name FROM Words AS w
INNER JOIN Users AS ua ON w.author_id = ua.id 
LEFT JOIN Users AS uw ON w.winner_id = uw.id

I'm a little lost as to how to do this in Knex. My first attempt didn't involve aliasing, and so I got a 'table used more than once' error. When I tried to use the .as() method, knex complained that there was a missing .from() clause. Is the .as() method only used for aliasing subqueries, and I shouldn't expect it to be used to alias tables?

Solution

  • There are two ways to declare an alias for identifier (table or column). One can directly give as aliasName suffix for the identifier (e.g. identifierName as aliasName) or one can pass an object { aliasName: 'identifierName' }.

    So, the following code:

     knex.select('w.*', 'ua.name', 'uw.name')
  .from({ w: 'Words' })
  .innerJoin({ ua: 'Users' }, 'w.author_id', '=', 'ua.id')
  .leftJoin({ uw: 'Users' }, 'w.winner_id', '=', 'uw.id')
  .toString()

    will compile to:

    select "w".*, "ua"."name", "uw"."name"
from "Words" as "w"
inner join "Users" as "ua" on "w"."author_id" = "ua"."id"
left join "Users" as "uw" on "w"."winner_id" = "uw"."id"