node.js - 复杂的过滤器 Sequelize js

Question

我有以下实体:

Product            [id, name]  
Properties         [id, name, product_id]  
Values             [id, name, property_id]  
Offer              [id, product_id]
OfferDetail        [offer_id, property_id, value_id]

我想在 sequelize 中执行以下过滤器:

product_id
[property_1, value_1], [property_2, value_2], ...

这将返回具有相应值的所有产品。

例子:

Products
[{1, Potato}, {2, Tomato}]

Properties
[{1, Color, 1}, {2, Size, 1}, {3, Size, 2}]

Values
[{1, White, 1}, {2, Red, 1}, {3, Small, 2}, {4, Big, 2}, {5, Medium, 2}, {6, Small, 3}, {7, Big, 3}]

Offer
[{1, 1}, {2, 1}, {3, 2}]   (I have 3 offers, 2 for potato and 1 for tomato)

OfferDetail
[{1, 1, 1}, {1, 2, 3}, {2, 1, 2}, {3, 3, 6}]  
meaning:
Potato: {Color White}, {Size Small}
Potato: {Color Red}
Tomato: {Size Small}

I want to be able to filter:
Potato Small
Potato (White or Red)

or a mix of them.

谢谢

Answer 1

对于这种类型的复杂查询，您可以尝试使用原始查询

select p.name, group_concat(distinct pr.name separator ',') as prNames, group_concat(distinct v.name separator ',') as prValues from OfferDetail od join Offer o on o.id=od.offer_id left join Properties pr on pr.id=od.property_id left join Values v on v.id=od.value_id left join Products p on p.id=o.product_id group by od.offer_id

你能检查一下这个查询吗？