How to do a group by in my query?

Questions and Answers Strapi Backend
1
System Information
  • Strapi Version: 3
  • Operating System: Windows 10
  • Database: PostgreSql
  • Node Version: 16.13.0’
  • NPM Version: 8.1.0’
  • Yarn Version:

I have a collection in which i save records with the views of each product per user in my app.

Each record has today’s date, the product id and the id of the user who viewed it.

Ex:

id created_at productid userid
1 2022-01-27T21:42:46.563Z 1 1
1 2022-01-27T21:42:46.563Z 1 2
1 2022-01-27T21:42:46.563Z 2 3
1 2022-01-27T21:42:46.563Z 1 3
1 2022-01-28T21:42:46.563Z 1 1
1 2022-01-28T21:42:46.563Z 1 2
1 2022-01-28T21:42:46.563Z 2 3
1 2022-01-28T21:42:46.563Z 3 3

In trying to do a query to get the number of views per day of one product:

Ex: Url: /product-views/report/:id:
{id} is the id of the product i want to consult, Let’s say for the example above, we choose the productid 1
I’m looking for this result:
{
“date”:“2022-01-27”,
“views”: 3
},
{
“date”:“2022-01-28”,
“views”: 2
}

I applied the filters, but i just could obtain the count for one date in a request like this:
count?product.id=1&created_at_gte=2022-01-26&created_at_lt=2022-01-27

Do you know any way to get a group by?

1 Like