Drizzle Team•2mo ago

How to use findMany() with distinct column values?

Hi Everyone 🙂 I’m using the findMany() query in my project to retrieve the latest records from an SQLite (libsql) database:

export const getLatestRecords = db.query.Record.findMany({
  with: {
    owner: true
  },
  limit: sql.placeholder('limit'),
  orderBy: (records, { desc }) => [desc(records.createdAt)]
});

export const getLatestRecords = db.query.Record.findMany({
  with: {
    owner: true
  },
  limit: sql.placeholder('limit'),
  orderBy: (records, { desc }) => [desc(records.createdAt)]
});

I want to modify it to exclude any records with duplicate ownerId column values. If more than one record exists for a specific ownderId, I would like to get one (the latest) and exclude all others. Does something like distinctOn already exist in Drizzle? I would highly appreciate any assistance. Thanks a lot🙏

4 Replies

rphlmr ⚡•2mo ago

Hi 👋 It exists but with the select API https://orm.drizzle.team/docs/select#distinct (postgres only for distinctOn)

Drizzle ORM - Select

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

n00ki•2mo ago

Thanks @Raphaël M (@rphlmr) ⚡! If that's the case, maybe you can try to help me think of a way to achieve the desired query? if I understand correctly, the absence of distinctOn means I can't use the findMany API for this specific case, right?

rphlmr ⚡•2mo ago

Yes that's it. Here a possible query: https://drizzle.run/u5l9ngj2nayw7ly7e5fhl8kg

Drizzle Run

Select distinct on - Drizzle Run

rphlmr ⚡•2mo ago

const LastUserRecord = db.$with("last_user_record").as(
  db
    .select({
      ...getTableColumns(Record),
      maxCreatedAt: max(Record.createdAt),
    })
    .from(Record)
    .groupBy(Record.userId),
);

const latestRecordsForUsers = await db
  .with(LastUserRecord)
  .select({
    id: LastUserRecord.id,
    content: LastUserRecord.content,
    createdAt: LastUserRecord.createdAt,
    owner: User,
  })
  .from(LastUserRecord)
  .innerJoin(User, eq(User.id, LastUserRecord.userId));

const LastUserRecord = db.$with("last_user_record").as(
  db
    .select({
      ...getTableColumns(Record),
      maxCreatedAt: max(Record.createdAt),
    })
    .from(Record)
    .groupBy(Record.userId),
);

const latestRecordsForUsers = await db
  .with(LastUserRecord)
  .select({
    id: LastUserRecord.id,
    content: LastUserRecord.content,
    createdAt: LastUserRecord.createdAt,
    owner: User,
  })
  .from(LastUserRecord)
  .innerJoin(User, eq(User.id, LastUserRecord.userId));

Gaming

Programming

How to use findMany() with distinct column values?