0

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
user3473184
  • 23
  • 1
  • 6
  • 1
    You used the words *grouped by*. In SQL parlance, I think you mean *ordered by*. `GROUP BY` is for aggregating things -- `SUM(whatever)` -- and `ORDER BY` is for sorting result sets. At any rate your question isn't clear. Please [edit] it to give more details, or ask another. – O. Jones Dec 30 '21 at 12:47
  • Updated with example of object I want to get – user3473184 Dec 30 '21 at 12:58
  • You _could_ use `json_agg` + `json_build_object` to return an array group by status, however, is it worth it over grouping it in JS after fetching all items like @O.Jones answer? - I'm not sure but anyway, take a look at https://stackoverflow.com/a/34163761/2956135 – Emma Dec 30 '21 at 18:39
  • Good idea, @Emma. But it puts an extra data-wrangling burden on the RDBMS server, which is typically the performance bottleneck in a scaled-up app. – O. Jones Dec 31 '21 at 11:01
  • @O.Jones, totally agree. That's why I wasn't recommending. It is good to know that function is available in SQL but don't know when that would be useful. – Emma Dec 31 '21 at 15:04

2 Answers2

2

.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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
0

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.

Robert Niestroj
  • 15,299
  • 14
  • 76
  • 119
  • Its not possible to use group by clause without aggregating data like SUM or MAX for example. I guess I need to run a for loop and change where clause each time, then group the data using JS – user3473184 Dec 30 '21 at 13:07