Drizzle Team•10mo ago

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'),
});

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);

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

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

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