2 replies

Circular reference in subquery

Have table

messages

messages

with following columns:

id

id

body

body

and

parent_id

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

id

parent_id

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)