DT
Drizzle Team•11mo ago
jedik

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
Mykhailo•11mo 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.
jedik
jedikOP•11mo ago
@solo Thanks a lot, this works, alias is exactly the piece I was looking for and missed in the docs.

Did you find this page helpful?