DT
Drizzle Team•6mo ago
n00ki

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 ⚡
rphlmr ⚡•6mo 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
n00kiOP•6mo 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 ⚡
rphlmr ⚡•6mo ago
rphlmr ⚡
rphlmr ⚡•6mo 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));

Did you find this page helpful?