Prisma2, groupBy Date

Question

I need to group fields by date, I use Prisma 2, for example, filter all register in month, but I don't know how to do it in the case of a date

Eu preciso agrupar os campos com um intervalo de data, por exemplo todos os registros dentro de um mês!

an example filter by normal field

await this.record.groupBy({
        by: ['category'],
        where: {
            AND: [
                { user: id },
                { account: { in: accountsIds } },
                { category: { in: categoriesIds } },
                { operation },
            ],
        },
        sum: {
            amount: true,
        },
    });

this return me:

{ category: 27, sum: { amount: -45 } },  { category: 29, sum: { amount: 4400 } }

i'm grateful for any help!

desde já agradeço qualquer ajudar!

Answer 1

Doing this with Prisma is definitely the hard way, even if its possible. Behind Prisma there is a database, which is more capable for jobs like this. For the example just assume this is postgresql.

For the particular problem, create a view, like

CREATE VIEW record_by_month AS
  SELECT date_trunc('month', YOURDATE) month,
  sum(amount) amount_by_month FROM YOURTABLE group by 1;

You have to add the other fields you need ofc. Create the prisma model, write the prisma query, and you are done.

More prismaish way, maybe it worths a try, (especially if you will write for this model many differnt queries with month group, like the above) to create a view for the particular date only:

CREATE VIEW record_by_month AS
  SELECT distinct YOURDATE id,
  date_trunc('month', YOURDATE) month FROM YOURTABLE;

Create a model, add it with @relation to the original table, and fiddle with groupBy and include, write something like by: [record_by_month.month] (not sure its possible in the current version). The clear advantage of this approach (if works), is that you don't have to duplicate too many parts of your model.