jedik
jedik
PostsComments
DTDrizzle Team
Created by jedik on 2/21/2024 in #help
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)
3 replies