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

I have a MySQL query that selects a

patch

patch

in the

patches

patches

table by

id

id

and then in the same query counts how many patches exist with the same

user_id

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

FROM patches p

and

WHERE user_id = p.user_id

WHERE user_id = p.user_id

.

If that's not possible then my alternative is to execute the query with

db.execute()

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")

updatedAt: datetime("updated_at")

I get the data back with a key of

updated_at

updated_at

, and as a

string

string

instead of a

Date

Date

object. I'd like to get the same kind of result as when I'm using

db.select().from(patches)

db.select().from(patches)

.
I'm guessing this is doable with the

.mapsWith()

.mapsWith()

function, but I haven't figured out how yet.

Let me know if you need any more information.

Drizzle Team•2y ago

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

I have a MySQL query that selects a

patch

patch

in the

patches

patches

table by

id

id

and then in the same query counts how many patches exist with the same

user_id

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

FROM patches p

and

WHERE user_id = p.user_id

WHERE user_id = p.user_id

.

If that's not possible then my alternative is to execute the query with

db.execute()

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")

updatedAt: datetime("updated_at")

I get the data back with a key of

updated_at

updated_at

, and as a

string

string

instead of a

Date

Date

object. I'd like to get the same kind of result as when I'm using

db.select().from(patches)

db.select().from(patches)

.
I'm guessing this is doable with the

.mapsWith()

.mapsWith()

function, but I haven't figured out how yet.

Let me know if you need any more information.

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

Similar Threads

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

Similar Threads

Similar Threads

Similar Threads