Alias a table in Knex

Question

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?

score 71 · Answer 1 · answered Feb 06 '15 at 08:23

I think I figured it out. In knex.js, say you specify a table like:

knex.select( '*' ).from( 'Users' )

Then you can just add the AS keyword within the quotes of the table name to alias it, like so:

knex.select( '*' ).from( 'Users AS u' )

..and you can do this for column names, too; so my original SQL would look like this in knex-land:

    knex.select( 'w.*', 'ua.name AS ua_name', 'uw.name AS uw_name' )
    .innerJoin( 'Users AS ua', 'author_id', 'ua.id' )
    .leftJoin( 'Users as uw', 'winner_id', 'uw.id' )

I guess I got confused by the presence of knex's .as() method, which (as far as I currently understand) is meant just for subqueries, not for aliasing tables or column names.

Note that shorthand alias syntax `Users us` does not work here.. one must provide the `AS` keyword. — jirkamat, Jun 18 '23 at 12:26

score 42 · Accepted Answer · answered Jul 17 '20 at 17:50

42

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"

answered Jul 17 '20 at 17:50

Евгений Евдокимов

436
5
3

1

update 2020 - I am not sure if this mechanism existed in Knex back when I originally asked this question, but this now appears to be the proper way to alias a table. Thanks! – Bobby Circle Ciraldo Jul 18 '20 at 20:04
1

Knex updated their docs and broke the link in the parent comment, it should point [here](http://knexjs.org/guide/query-builder.html#identifier-syntax) now – Schleicher Jun 06 '22 at 19:18

Dmitriy Botov · Answer 3 · 2018-09-13T06:16:19.613

Found this question when tried to figure out how to select all columns from all joined tables without overriding one another if column names are equals. This is how I did it, prepending each with its "tablename_":

const columnToText = (table, column) => `${table}.${column} as ${table}_${column}`;
const prepareColumns = async (table) => {
  const columnsInfo = await knex(table).columnInfo();
  return Object.keys(columnsInfo).map(column => columnToText(table, column));
};

const selectColumns = (await Promise.all([
  'joined_table1',
  'joined_table2',
  'main_table',
].map(prepareColumns)))
  .reduce((acc, item) => ([...acc, ...item]), []);

const data = await knex('main_table')
  .leftJoin('joined_table1', 'main_table.joined_table1_id', 'joined_table1.id')
  .leftJoin('joined_table2', 'main_table.joined_table1_id', 'joined_table2.id')
  .select(...selectColumns);

Hope this is relevant and will help someone – Dmitriy Botov Sep 12 '18 at 10:34 — Dmitriy Botov, Sep 12 '18 at 10:34

Rigel1121 · Answer 4 · 2015-02-06T03:14:58.510

-2

Aliasing or the .as() method is not only used for subqueries, it can also be used in aliasing column names and tables. In your case you need to use alias to your table because you used the table twice. In your first attempt, It got an error because your SQL got confused. By putting an aliases on the table that is used twice, you gave it an identification that ua.Users table is now different to uw.Users table. Hope it helped you.

edited Feb 06 '15 at 03:14

answered Feb 06 '15 at 02:10

Rigel1121

2,022
1
17
24

1

I could be wrong, but it sounds a little bit like your answer applies to SQL in general, but not specifically knex.js. I know that my SQL is correct, but I'm trying to figure out what it would look like expressed in knex-speak. – Bobby Circle Ciraldo Feb 06 '15 at 02:29
How do you use it to alias column names? `.column('x').as('y')` aliases the *table* to 'y', *not* 'x'. – mpen May 04 '16 at 22:02
It's done the same way as table names, and there's an example of aliasing columns in my answer above. ("Users" is a column name, and I'm aliasing it to "ua".) Sorry for the late reply, ha! – Bobby Circle Ciraldo Oct 19 '17 at 16:29

Alias a table in Knex

4 Answers4

Linked