How to select specific columns from a joined table in Knex.js?

Question

I have been working with SQL databases for about 20 years and I don’t seem to understand how to use the Knex method to match queries. Can someone help me get the correct code?

I have this SQL query that I want to use in a nodejs application:

SELECT p.id, p.project_number, p.project_name, p.start_date, p.end_date, o.name, o.email_addr, c.company, c.email_addr AS company_email, c.first_name, c.last_name FROM projects p INNER JOIN owners o ON o.id = p.owner_id INNER JOIN clients c ON c.id = p.client_id 

The KnexJS documentation (0.7.5) shows an example that would look like this for my query:

 knex.from('projects').innerJoin('owners', 'owners.id', 'projects.owner_id') .innerJoin('clients', 'clients.id', 'projects.client_id'); 

There are a few things that I cannot find in the documentation:

1) how to choose which columns I want to include? Projects, clients and owners each have 20-50 columns, and I am not interested in them. Selecting columns from the main table is clear (using select () or column () ), but how do I select columns from joined tables?

2) Some columns have the same name. How to avoid name conflicts (i.e. add a prefix to columns from other tables)? I looked at how Knex can generate column aliases (... AS ...), and I'm not sure if this is a viable option for more complex queries. (Even for relatively simple queries like above)

Answer 1

You may try:

 knex('projects').select(['projects.id', 'projects.project_number', 'projects.project_name', 'projects.start_date', 'projects.end_date', 'owners.name', 'owners.email_addr as owner_email_addr', 'clients.company', 'clients.email_addr as client_email_addr', 'clients.first_name', 'clients.last_name']) .innerJoin('owners', 'owners.id', 'projects.owner_id') .innerJoin('clients', 'clients.id', 'projects.client_id'); 

Hope this helps!

Answer 2

In this answer I will tell more than just the choice and the question itself!

selection and prevention of name conflicts

The principle of choice is to use select . And name conflicts can be avoided by renaming columns (aliases). And to create aliases (renaming a column), simply use as in the column name row, as shown below:

 knex .select([ 'table_a.id', 'table_b.id as b_id' // ---> the way to create aliases (to avoid ]) // columns overlapping and overriding .from('table_a') .leftJoin( 'table_b', 'table_a.table_b_id', 'table_b.id' ); 

It will produce this SQL:

 SELECT "table_a"."id", "table_b"."id" as "b_id" FROM "table_a" LEFT JOIN "table_b" ON "table_a"."table_b_id" = "table_b"."id" 

We can formulate in different ways. Some interesting ones as shown below:

 knex('processes') // <-- wihtout using from (shorter) [less sql like though] .select([ 'processes.id as processId', 'processes.name as processName', 'processes.description as processDescription', 'processes.deleted as processDeleted', 'processes.deleteTime as processDeleteTime', 'rsp.runningSettingId', 'rsp.value as settingValue', 'rsp.startTime as settingStartTime' ]) .innerJoin( 'runningSettings_processes as rsp', 'processes.id', 'rsp.processId' ); 

Examples of multiple unions (3 or more)

 const superJoinData = await knex( // i'm using async await for the promises knex<Process>('process')//__________________________from here .select([ 'processes.id as processId', 'processes.name as processName', //--- conflict with rs.name 'processes.description as processDescription', //-- conflict with rs.description 'processes.deleted as processDeleted', 'processes.deleteTime as processDeleteTime', 'rsp.runningSettingId', 'rsp.value as settingValue', 'rsp.startTime as settingStartTime' ]) .innerJoin( 'runningSettings_processes as rsp', 'processes.id', 'rsp.processId' )//_______________________to here .as('prsp') // |first join. (result an equivalent of a table) ) // !!!!!!!!!!!!!!!!! notice how we nested a knex construct within another!! .select([ // select from the resulting table of the first join !!!! 'prsp.processId', 'prsp.processName', 'prsp.processDescription', 'prsp.processDeleted', 'prsp.processDeleteTime', 'prsp.runningSettingId', 'prsp.settingValue', 'prsp.settingStartTime', 'rs.name as settingName', 'rs.description as settingDescription' ]) .innerJoin( // ______________second inner join 'runningSettings as rs', 'prsp.runningSettingId', 'rs.id' ); 

Note how to use the as() method!

additional (not related to the choice, but creates and combines)

Filtering with:

 knex( knex('A').where('A.id',1).as('t1') ).leftJoin( knex('B').where('B.id', 2).as('t2'), 't1.c', 't2.d' ) //or filter the resulting table of the join knex('A') .leftJoin( knex('B').where('B.id', 2).as('t2'), 't1.c', 't2.d' ) .where('someColumn', 2); 

Using callbacks for further control

 knex .select('*') .from(function () { this.select('*').from('A') .where('id',1) .as('t1'); }) .leftJoin( knex('B').where('id',2).as('t2'), function () { this.on('t1.c', '=', 't2.d'); // <--- notice also the use of function } // construct for the join condition ); 

Same as above, without function construction (and with several join conditions as a bonus)

 knex( // <--- notice the knex constructor (equivTo: knex.select().from()) knex('A') //NT: we already used that above when answering the question .select('*') .where('id',1) .as('t1'); ) .leftJoin( knex('B').where('id',2).as('t2'), function () { // <----- notice that in place of using the 2nd and 3d arg, we can have a function in the 2nd arg this.on('t1.c', '=', 't2.d').on('t1.s', '=', 't2.g'); // <-----| } // plus: multiple on conditions______/ );