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!

In general, I prefer question posts to contain at least one explicit question - if more than one, they should be closely related ***or*** asked in separate posts. For a database question, consider presenting relevant parts of the schema and sample data. — greybeard, May 17 '21 at 10:13

score 3 · Answer 1 · answered Aug 10 '21 at 20:26

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.

Prisma2, groupBy Date

1 Answers1