knex subquery leftJoin/innerJoin/rightJoin/leftOuterJoin

[AlfieriChou]

• 参考issue

• example

knex('orders')
  .select(
    'orders.*', 
    knex.raw('coalesce(??, 0) as ??', ['x.unread', 'unread_messages'])
  )
  .leftJoin(
    knex('charts')
      .select('id_order', knex.raw('count(*) as ??', ['unread']))
      .where('read_by_use', 0).groupBy('id_order').as('x'), 
    'x.id_order', 
    'orders.id_order'
  )
  .where('id_customer', 42)
  .orderBy('date_submitted')

• sql

select 
  `orders`.*, coalesce(`x`.`unread`, 0) as `unread_messages` 
from `orders` 
left join (
  select `id_order`, count(*) as `unread` 
  from `charts` 
  where `read_by_use` = ? 
  group by `id_order`
) as `x` 
on `x`.`id_order` = `orders`.`id_order` 
where `id_customer` = ? 
order by `date_submitted` asc