Drizzle Team•3mo ago

How to `as` a table name

Hey 👋 , I have a parent query that use a table let's say A, and in this parent query it contains another query which will also use A, how in drizzle can I as the table name for the sub query ? The code would roughtly look like so:

const childQ = db.select({
    id: A.id --> need to joined A
}).from(B).leftJoin(A, eq(B.childrenIDs, A.id --> need to use joined A)).where(eq(B.id, A.id --> need to use A from parent ))

db.select({
    parentId: A.id,
    childIds: 
}).from(A).leftJoin(B, eq(A.id, B.id))

const childQ = db.select({
    id: A.id --> need to joined A
}).from(B).leftJoin(A, eq(B.childrenIDs, A.id --> need to use joined A)).where(eq(B.id, A.id --> need to use A from parent ))

db.select({
    parentId: A.id,
    childIds: 
}).from(A).leftJoin(B, eq(A.id, B.id))

🙏

3 Replies

KiwibeOP•3mo ago

the best I can come up right now is:

          SELECT count(Abis.id)
              FROM ${getTableName(A)} as Abis
              WHERE B.id = A.id

          SELECT count(Abis.id)
              FROM ${getTableName(A)} as Abis
              WHERE B.id = A.id

wrapped in a sql

`
But what I'd like is to build the query with drizzle and then do a

sql.raw(query.toSQL().sql)`

Mario564•3mo ago

You can try using the alias function

KiwibeOP•3mo ago

🙏

Gaming

Programming

How to `as` a table name