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

Question

I have worked about 20 years with SQL databases and seem to have trouble understanding Knex way to map queries. Can someone help me get the correct code?

I have this SQL query that I want to use in my 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

KnexJS (0.7.5) documentation shows 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 couple of things I cannot find in the documentation:

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

2) Some columns have identical names. How can I avoid name conflicts (i.e. add some prefix to columns from other tables)? I looked at the way Knex can generate column aliases (... AS ...) and I am not sure it is a viable option for more complex queries. (Even for relatively simple queries like the one above)

Answer 1

You can 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 cover more then just selecting and the question itself !

selecting and avoiding name conflicts

The principle to select would be to use select. And name conflicts can be avoided by renaming the columns (aliasing). And to create aliases (column renaming) just use as within the column name string as in the snippet bellow:

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 are as in bellow:

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'
);

Multiple joins examples (3 and 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'
    );

Notice how to use as() method!

 extra (unrelated to select, but constructs and join)

Filtering with where:

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 construct (and with multiple 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______/
);