Drizzle Team•9mo ago

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

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}`
)

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.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

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