gpt4 book ai didi

mysql - MySQL 左连接上的 Knex.js 子查询

转载 作者:行者123 更新时间:2023-12-01 09:14:31 28 4
gpt4 key购买 nike

我是 Knex.js 查询构建器的新手,我目前在使用某种简单的 MySQL 选择时遇到了麻烦。这里是 :

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 的文档时有点迷茫,但是我应该将 .joinRaw 用于连接,将 knex.raw 用于合并命令吗?

最佳答案

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

关于mysql - MySQL 左连接上的 Knex.js 子查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48098061/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com