Arangodb groupby query with paging

Question

I have a database of products in an arangodb collection in which a product has multiple sizes. The issue is that for each size, the same product is repeated. But each product has a common group number. Like this:

{"name": "product1", "description": "someDescription", size: 5, groupNumber: 12}
{"name": "product1", "description": "someDescription", size: 15, groupNumber: 12}
{"name": "product1", "description": "someDescription", size: 25, groupNumber: 12}
{"name": "product1", "description": "someDescription", size: 35, groupNumber: 12}

{"name": "product2", "description": "someDescription", size: 5, groupNumber: 11}
{"name": "product2", "description": "someDescription", size: 15, groupNumber: 11}
{"name": "product2", "description": "someDescription", size: 25, groupNumber: 11}
{"name": "product2", "description": "someDescription", size: 35, groupNumber: 11}

I have to now display the list of products(in a web page) but each product should appear only once with sizes in an array for each product like this:

product1 someDescription sizes: 5,15,25,35
product2 someDescription sizes: 5,15,25,35
...

Each page should contain upto 25 products. I wonder how to do this using arangodb and is it at all possible?

score 2 · Accepted Answer · answered Feb 13 '20 at 19:11

About the first problem, grouping your products, that can be done with COLLECT. Assuming you get your products from a collection col, you can write

FOR doc IN col
COLLECT groupNumber = doc.groupNumber, name = doc.name, description = doc.description INTO sizes = doc.size
RETURN CONCAT(name, " ", description, " sizes: ", CONCAT_SEPARATOR(",", sizes))

.

With your example data, this gives

[
  "product2 someDescription sizes: 5,15,25,35",
  "product1 someDescription sizes: 5,15,25,35"
]

. You probably want to add a SORT statement after COLLECT to get the desired order.

To get to your second problem, i.e. pagination, use LIMIT. E.g.

FOR doc IN col
COLLECT groupNumber = doc.groupNumber, name = doc.name, description = doc.description INTO sizes = doc.size
LIMIT @offset, 25
RETURN CONCAT(name, " ", description, " sizes: ", CONCAT_SEPARATOR(",", sizes))

and set the bind parameter offset to 0 to fetch the first page, 25 for the second, and so on.

If you want a dynamic page size, just replace LIMIT @offset, 25 with LIMIT @offset, @limit and add the additional bind parameter.

Can you also have a look at https://stackoverflow.com/questions/60240849/arangodb-groupby-query-multiple-fields ? — rahulserver, Feb 15 '20 at 16:42

Arangodb groupby query with paging

1 Answers1