According to the Shopware 6 search query documentation, you can use a group by statement on your query by using the "grouping" parameter.
https://shopware.stoplight.io/docs/store-api/cf710bf73d0cd-search-queries#grouping
The text suggests that you can, for instance, get orders grouped by customer. But I haven't been able to get it to work:
{
"grouping": ["orderCustomer"]
}
{
"grouping": ["orderCustomer.id"]
}
I've tried including the orderCustomer and the customer association, (and all kinds of likely and unlikely stuff), but that didn't make any difference.
Maybe it isn't possible, and I'm just interpreting the explanation wrong.
Anybody have an idea? It would be really useful, if you could do that.
The order_customer entity is a copy, a snapshot if you will, of the customer state when the order is placed. This is done because a customer might change his data in the future, like his address and so on. In that case you wouldn't want orders placed by this customer in the past to be retroactively changed, e.g. show a different shipping address than the one the wares have been shipped to. That also means every order_customer is unique to every order. So grouping by the id of that association is effectively pointless.
The order_customer entity has an association to the actual customer entity. You will want to use that association for grouping instead.
Additionally when you use grouping for an association you have to use a filter on the same association or otherwise the join will be missing from the pre-search query. This is currently a quirk of the implementation of the grouping and should be fixed in the future.
{
"associations": {
"orderCustomer": {
"associations": {
"customer": {}
}
}
},
"filter": [
{
"type": "not",
"operator": "or",
"queries": [
{
"type": "equals",
"field": "order.orderCustomer.customer.id",
"value": null
}
]
}
],
"grouping": ["order.orderCustomer.customer.id"]
}