Drizzle TeamDT
Drizzle Team•2y ago
fz

Using correlated subqueries / transforming response from sql`SELECT * ...` to match `db.select()`

I have a MySQL query that selects a patch in the patches table by 
id
 and then in the same query counts how many patches exist with the same 
user_id
 as the one selected.

Here is my current query in full:

SELECT
    p.*,
    (SELECT COUNT(*) FROM patches WHERE user_id = p.user_id) AS total_count
FROM patches p
WHERE p.id = 22


Is it possible to recreate this query using Drizzle's API? I'm getting stuck on how to recreate FROM patches p and WHERE user_id = p.user_id.

If that's not possible then my alternative is to execute the query with db.execute() like this:

const p = await db.execute(
    sql`
    SELECT
        p.*,
        (SELECT COUNT(*) FROM patches WHERE user_id = p.user_id) AS total_count
    FROM patches p
    WHERE p.id = ${patchId}`
)


My problem with that solution is that it returns the data straight from MySQL, so I don't get row names and types that match what I've defined in my Drizzle schema, for example for a row defined as updatedAt: datetime("updated_at") I get the data back with a key of updated_at, and as a string instead of a Date object. I'd like to get the same kind of result as when I'm using db.select().from(patches).
I'm guessing this is doable with the .mapsWith() function, but I haven't figured out how yet.

Let me know if you need any more information.
Was this page helpful?