How to order by included table's JSONB field #14677
Unanswered
sennmac
asked this question in
Help & Questions
Replies: 2 comments
-
You don't need to specify the model if you use a literal or fn: Budget.findAll({
include: [BudgetDetail],
order: [fn('TO_NUMBER', literal(`"budgetDetail".detail->>'amount'"`)],
}); |
Beta Was this translation helpful? Give feedback.
0 replies
-
Thanks for your reply. Again, thank you.~ |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
hi
dear devsI have some tables a like
budget
{id:''}
budgetDetail
{id:'',budgetIdl:'',detail:{amount:1}}
I need to search on the budget table and include the budgetDetail table then order by the detail->amount.
so I write code like
Budget.findAll({include:[budgetDetail],order:[Models.budgetDetail,Models.sequelize.literal("detail->>'amount'")]})
but you know the amount is a text in jsonb, so I need to convert it into a number.
Budget.findAll({include:[budgetDetail],order:[Models.budgetDetail, Models.sequelize.fn('TO_NUMBER', Models.sequelize.literal("budgetDetail".detail->>'amount'"),]})
But the raw SQL will render into
ORDER BY "budgetDetail".TO_NUMBER("detail->>'amount'") ASC
which will fail.
Beta Was this translation helpful? Give feedback.
All reactions