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

Hi all,

TLDR:
It seems that the

selectDistinct

selectDistinct

function does not exist on the object I get after I do

db.with(...)

db.with(...)

.
If I remove the

with

with

then

selectDistinct

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

selectDistinct

function does not exist on the object I get after I do

db.with(...)

db.with(...)

.
If I remove the

with

with

then

selectDistinct

selectDistinct

exists, but that breaks my query.

What am I missing here?

Thanks

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

selectDistinct

function does not exist on the object I get after I do

db.with(...)

db.with(...)

.
If I remove the

with

with

then

selectDistinct

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

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

selectDistinct

function does not exist on the object I get after I do

db.with(...)

db.with(...)

.
If I remove the

with

with

then

selectDistinct

selectDistinct

exists, but that breaks my query.

What am I missing here?

Thanks

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

Similar Threads

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

Similar Threads

Similar Threads

Similar Threads