I'm kind of new with Knex.js query builder and I'm currently having trouble with one somehow simple MySQL select. Here it is :
SELECT orders.*, coalesce(x.unread, 0) AS unread_messages
FROM orders
LEFT JOIN
(SELECT id_order, COUNT(*) AS unread
FROM chats
WHERE read_by_user = 0
GROUP BY id_order) AS x
ON x.id_order = orders.id_order
WHERE id_customer = 42
ORDER BY date_submitted;
I'm a bit lost reading Knex's doc, but should I use .joinRaw for the join and knex.raw for the coalesce command ?
https://runkit.com/embed/1olni3l68kn4
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')
produces
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