我try 使用aggregate
和$lookup
将PostgreSQL查询(来自Vercel的nextjs仪表板教程)转换为MongoDB查询.
我知道如何聚合多个集合并为一个参数进行筛选.以及如何用$or
运算符查询一个集合的多个参数.
我遇到的问题是,查询使用多个搜索参数搜索多个集合.我想不出一种方法来将其合并到一个聚合管道中.
我搜索了现有的问题,但只找到一个问题,上面说这是不可能的. 所以我的问题是,这是可能的吗?如果可能,如何实现?
这就是SQL的样子:
SELECT
invoices.customer_id,
invoices.amount,
invoices.status,
customers.name
FROM invoices
JOIN customers ON invoices.customer_id = customers.id
WHERE
customers.id ILIKE ${`%${query}%`} OR
customers.name ILIKE ${`%${query}%`} OR
invoices.amount::text ILIKE ${`%${query}%`} OR
invoices.status ILIKE ${`%${query}%`}
ORDER BY invoices.amount DESC
以下是这些Collection 品的外观:
db={
"invoices": [
{
"customer_id": "10",
"amount": 15795,
"status": "pending"
},
{
"customer_id": "20",
"amount": 20348,
"status": "paid"
}
],
"customers": [
{
"id": "10",
"name": "Delba de Oliveira"
},
{
"id": "20",
"name": "Lee Robinson"
}
]
}