Drizzle TeamDT
Drizzle Team•2y ago
Yuval Peled

Is it possible to "select distinct" while using a "with clause"?

Hi all,

TLDR:
It seems that the selectDistinct function does not exist on the object I get after I do db.with(...).
If I remove the with then selectDistinct exists, but that breaks my query.


Full details:
I have the following tables:
const conversations = pgTable('conversations' {
  id: varchar('id'),
  createdAt: timestamptz('created_at'),
});

const messages = pgTable('messages', {
  id: varchar('id'),
  conversationId: varchar('conversation_id'),
  content: varchar('content'),
});

(The real use case has many more columns, but this is a minimally viable reproduction).

I'd like to execute a select query that utilize a "with" clause and then a "distinct on". It should look like this:

const withClause = db.$with('filtered_conversations').as(
  db.select()
    .from(conversations)
    .join(messages, eq(messages.conversationId, conversations.id)
    .where(ilike(messages.content, `%${some_param}%`))
);
const result = db.with(withClause)
  .selectDistinct({ id: withClause.id })
  .from(withClause);


However, it seems that the selectDistinct function does not exist on the object I get after I do db.with(...).
If I remove the with then selectDistinct exists, but that breaks my query.

What am I missing here?

Thanks
Was this page helpful?