CODEWITHSUNDEEP
javascriptsqlprisma
programandoconro
programandoconro

Reputation: 2709

Prisma client query for latest values of each user

I am new to prisma-client and I want to return the latest value register by each user of a given company.

This is my schema.prisma:

model Locations {
    id        Int    @id @default(autoincrement())
    user      String
    company   String
    latitude  String
    longitude String
    timestamp String
}

This is what I have tried so far:

const lastLocations = await db.locations.findMany({
    where: {
      company,
    },
    orderBy: {
      id: 'desc',
    },
    take: 1,
  });

But I need to get 1 value for each user, I solved this previously in sql with:

WITH ranked_messages AS (   SELECT m.*, ROW_NUMBER() OVER (PARTITION BY userID ORDER BY timestamp DESC) AS rn FROM locations AS m ) SELECT * FROM ranked_messages WHERE rn = 1 AND companyID = "${companyID}";`;

But I have no idea how to do proceed in prisma. Is there an "each" method?

I appreciate any help. Thank you.

Upvotes: 3

Views: 6490

Answers (1)

programandoconro
programandoconro

Reputation: 2709

I solved it using the distinct option:

const lastLocations = await db.locations.findMany({
    where: {
      company,
    },
    distinct: ['user'],
    orderBy: {
      id: 'desc',
    },
  });

Thanks.

Upvotes: 11

Related Questions