通过JSONB数据类型的订购，并续集字面
目前，我正在使用此结构查询所有项目：
const filteredItems = await allItems.findAll({
where: conditions,
include: associations,
order: sortingCriteria,
limit: limit,
offset: offset,
});
sortingcriteria当前具有以下结构：
const sortingCriteria = [
["price", "ASC NULLS LAST"],
["created_at", "DESC NULLS LAST"],
]
price and create_at <<代码> Allitems 表。在同一张表中，我有另一个名为详细信息具有 JSONB 每项。
我需要添加一个额外的排序标准，因此我按照JSONB中可以找到的一些特定数据来订购以下结构：
details = {
"sizes": {
"height": 10,
"width": 20,
},
"capacities": {
"volume": 200,
"weight": 2,
}
}
假设我想通过
中卷订购所有项目（以及price和create_at_at，我已经拥有）。我应该如何将其包括在sortingcriteria？
使用JSONB代替嵌套表和连接的原因是表演。我已经尝试过，并且在一张桌子上所有的东西，对于巨大的桌子来说，性能大大提高。我将限制和offset用于分页目的。我正在使用续集6.6.2。
I'm currently using this structure to query for all the items:
const filteredItems = await allItems.findAll({
where: conditions,
include: associations,
order: sortingCriteria,
limit: limit,
offset: offset,
});
The sortingCriteria currently has this structure:
const sortingCriteria = [
["price", "ASC NULLS LAST"],
["created_at", "DESC NULLS LAST"],
]
Being price and created_at fields of the allItems table. In the same table I have another field called details that has a JSONB per item.
And I need to add an extra sorting criteria, so I order by some specific data that can be found in that JSONB, with this structure:
details = {
"sizes": {
"height": 10,
"width": 20,
},
"capacities": {
"volume": 200,
"weight": 2,
}
}
Let's say I want to order by
volumeall the items (and also bypriceandcreated_at, as I already have). How should I include that insortingCriteria?
The reason of using a JSONB instead of nested tables and JOINs is performace. I have already tried and, having everything in one table, for huge tables, the performance increases tremendously. I use limit and offset for pagination purposes. I'm using Sequelize version 6.6.2.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对于
order，用于嵌套的JSONB列，您必须使用sequelize.literal使用- ＆gt;＆gt;＆gt ;操作员。希望它有帮助
For the
orderfor the nestedJSONbcolumn, you'll have to use thesequelize.literalmethod with the->>operator.Hope it helps