Alias a table in Knex

sql left-join inner-join knex.js
Bobby Circle Ciraldo picture Bobby Circle Ciraldo · Feb 6, 2015 · Viewed 16.9k times · Source

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?

Answer

Bobby Circle Ciraldo picture Bobby Circle Ciraldo · Feb 6, 2015

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.

Related questions

Using left join and inner join in the same query

Below is my query using a left join that works as expected. What I want to do is add another table filter this query ever further but having trouble doing so. I will call this new table table_3 and want …

Read More
SQL - inner join 2 tables but return all if 1 table empty

I have 2 tables say A and B, and I want to do a join on them. Table A will always have records in it. When table B has rows in it, I want the query to turn all the rows …

Read More
How to left join or inner join a table itself

I have this data in a table, for instance, id name parent parent_id 1 add self 100 2 manage null 100 3 add 10 200 4 manage null 200 5 add 20 300 6 manage null 300 How can I left join or inner join this table itself so I get this result …

Read More