javascript - Prisma client query for latest values of each user - Stack Overflow

admin管理员组

文章数量:1289483

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

This is my schema.prisma:

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

This is what I have tried so far:

const lastLocations = await db.locations.findMany({
    where: {
      pany,
    },
    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 panyID = "${panyID}";`;

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

I appreciate any help. Thank you.

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

This is my schema.prisma:

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

This is what I have tried so far:

const lastLocations = await db.locations.findMany({
    where: {
      pany,
    },
    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 panyID = "${panyID}";`;

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

I appreciate any help. Thank you.

• javascript
• sql
• prisma

Share Improve this question Follow edited Jan 24, 2022 at 13:46 programandoconro asked Jan 24, 2022 at 13:20 programandoconroprogramandoconro 2,72922 gold badges2424 silver badges4141 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 11

I solved it using the distinct option:

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

Thanks.

本文标签： javascriptPrisma client query for latest values of each userStack Overflow