mysql left join上的knex.js子查询

nhjlsmyf 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(581)

我对knex.js query builder有点陌生，但我目前在使用一个简单的mysql select时遇到了问题。在这里：

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;

我有点看不懂knex的doc了，但是我应该用.joinraw来表示join，用knex.raw来表示coalesce命令吗？

mysql knex.js

来源：https://stackoverflow.com/questions/48098061/knex-js-subqueries-on-mysql-left-join

2条答案

按热度按时间

xoefb8l81#

对于那些登陆这里的人：这是我在@mikael的帮助下的工作方案。

selectFromWhere = db('orders')
  .select('orders.*', db.raw('IFNULL(??, 0) as ??', ['x.unread', 'unread_messages']))
  .leftJoin(
    db('chats')
      .select('id_order', db.raw('count(*) as ??', ['unread']))
      .where('read_by_user', 0)
      .groupBy('id_order')
      .as('x'),
    'x.id_order',
    'orders.id_order'
  )
  .where('id_customer', req.user.id_customer);

赞(0）回复(0）举报 2021-06-21

pkbketx92#

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

生产

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

赞(0）回复(0）举报 2021-06-21

我来回答

mysql left join上的knex.js子查询

2条答案

相关问题

热门标签

最新问答