Drizzle Team•10mo ago

Circular reference in subquery

Have table messages with following columns: id, body and parent_id (ref to parent message from the same table). Now select all messages and for each messages return also count of messages that have this message's id as parent_id. The SQL query is roughly:

SELECT 
    m.id,
    m.parent_id,
    m.body,
    (SELECT COUNT(*) FROM messages m2 WHERE m2.parent_id = m.id) AS child_count
FROM 
    messages m;

SELECT 
    m.id,
    m.parent_id,
    m.body,
    (SELECT COUNT(*) FROM messages m2 WHERE m2.parent_id = m.id) AS child_count
FROM 
    messages m;

Is it possible to rewrite it to Drizzle query? Something like:

db.select({..., repliesCount: ???)}).from(messages)

db.select({..., repliesCount: ???)}).from(messages)

2 Replies

Mykhailo•10mo ago

Hello, @jedik! You can try this, but also you should cast count to proper type in your query

const parent = alias(messages, 'm2');

const sq = db
  .select({
    count: count(),
  })
  .from(parent)
  .where(eq(parent.parentId, messages.id))
  .getSQL();

const response = await db
  .select({
    id: messages.id,
    body: messages.body,
    parentId: messages.parentId,
    childCount: sql<number>`(${sq})`,
  })
  .from(messages);

const parent = alias(messages, 'm2');

const sq = db
  .select({
    count: count(),
  })
  .from(parent)
  .where(eq(parent.parentId, messages.id))
  .getSQL();

const response = await db
  .select({
    id: messages.id,
    body: messages.body,
    parentId: messages.parentId,
    childCount: sql<number>`(${sq})`,
  })
  .from(messages);

Useful docs https://orm.drizzle.team/docs/joins#aliases--selfjoins https://orm.drizzle.team/docs/select#aggregations https://orm.drizzle.team/docs/sql

Drizzle ORM - Magic sql`` operator

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

Drizzle ORM - Joins

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

Drizzle ORM - Select

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

jedikOP•10mo ago

@solo Thanks a lot, this works, alias is exactly the piece I was looking for and missed in the docs.

Gaming

Programming

Circular reference in subquery