I have a Node.JS project and am using sequelize as the ORM. I would like to run a query where I get all of my orders grouped by the status. So I would get for example an object with 5 keys (given that I have 5 different statuses). And in each key there would be an array with all orders that have that status.
Example of object:
{
"Done": [{id:1, item: "bag"}, {id:2, item: "purse"}],
"Processing": [{id:3, item: "bag"}, {id:4, item: "purse"}],
"Pending": [{id:5, item: "bag"}, {id:6, item: "purse"}]
}
Is this possible in any way or I need to get all possible statuses and just run the same number of queries each time changing the where clause?
Thanks
.findAll() gives you back a result set consisting of an array of model instances. I guess in your case it will be an array of Orders. It's flattened, not hierarchical.
That will look something like this.
[
{status: "Done", id:1, item: "bag"},
{status: "Done", id:2, item: "purse"},
{status: "Processing", id:3, item: "bag"},
{status: "Processing", id:4, item: "purse"},
{status: "Pending", id:5, item: "bag"},
{status: "Pending", id:6, item: "purse"}
]
You can then turn that flattened result set into the hierarchical one you want. Something like this, not debugged, should do the job.
const byStatus = {}
for (const row of resultSet) {
/* first time we've seen this status value? if so create an array for it. */
if (!byStatus[row.status]) byStatus[row.status] = []
/* put the present row into the appropriate array */
byStatus[row.status].push( {id: row.id, item: row.item })
}
That way you can run just one .findAll() query. That's a good idea because it's more efficient than running multiple ones.
You need to use a "native" query with a GROUP BY clause or you fetch the data normally and do the gruoping in JS/TS using lodash's groupBy.