Attempting to get a Joined Subquery query into drizzle crud api

I was trying to get the following native sql query into a drizzle query

SELECT
    au.id,
    au.firstname,
    au.surname,
    ll."createdAt" AS latestlogindate,
    ll."numberoflogins" AS numberoflogins
FROM
    public.auth_user AS au
INNER JOIN
    (
        SELECT
            "authUserId",
            MAX("createdAt") AS "createdAt",    
            COUNT("createdAt") AS "numberoflogins"
        FROM
            public.login_log
        GROUP BY
            "authUserId"
    ) AS ll ON au.id = ll."authUserId"
ORDER BY
    ll."createdAt" DESC NULLS LAST

SELECT
    au.id,
    au.firstname,
    au.surname,
    ll."createdAt" AS latestlogindate,
    ll."numberoflogins" AS numberoflogins
FROM
    public.auth_user AS au
INNER JOIN
    (
        SELECT
            "authUserId",
            MAX("createdAt") AS "createdAt",    
            COUNT("createdAt") AS "numberoflogins"
        FROM
            public.login_log
        GROUP BY
            "authUserId"
    ) AS ll ON au.id = ll."authUserId"
ORDER BY
    ll."createdAt" DESC NULLS LAST

according to the docs for subqueries and aggregations I did the following

    const sq = db
        .select({
            id: loginlog.userId,
            numberOfLogins: sql<number>`count(${loginlog.createdAt})`,
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`,
        })
        .from(loginlog)
        .groupBy(loginlog.userId)

    return await db
        .select({
            id: user.id,
            firstname: user.firstname,
            surname: user.surname,
            // somehow need to get the values of the innerjoined subquery
            // ll."createdAt" AS latestlogindate,
            // ll."numberoflogins" AS numberoflogins
            // maybe something like:
            // latestLogin: sq.latestLoginDate,
        })
        .from(user)
        .innerJoin(sq, eq(sq.id, user.id))
        .orderBy(desc(sq.latestLoginDate))
        .limit(limit)

    const sq = db
        .select({
            id: loginlog.userId,
            numberOfLogins: sql<number>`count(${loginlog.createdAt})`,
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`,
        })
        .from(loginlog)
        .groupBy(loginlog.userId)

    return await db
        .select({
            id: user.id,
            firstname: user.firstname,
            surname: user.surname,
            // somehow need to get the values of the innerjoined subquery
            // ll."createdAt" AS latestlogindate,
            // ll."numberoflogins" AS numberoflogins
            // maybe something like:
            // latestLogin: sq.latestLoginDate,
        })
        .from(user)
        .innerJoin(sq, eq(sq.id, user.id))
        .orderBy(desc(sq.latestLoginDate))
        .limit(limit)

but it seems I can't join a subquery in drizzle sql. Is there another way to do it (besides a native sql query where I'll have to somehow type the results). Thnx P.S.: Can I call CONCAT (firstname, ' ', surname) or firstname || ' ' || surname from within the drizzle crud query ?

23 Replies

Luxaritas•14mo ago

What specific issue are you running into when joining the subquery? For the final bit, you’d need to use the sql template string, interpolating firstname and surname (from your schema objects) in the correct place in the query string

kratious•14mo ago

I think the issue is with converting the Select into a Subquery. Append an .as at the end of sq, this mirrors exactly what you have in the native SQL

    const sq = db
        .select({
            id: loginlog.userId,
            numberOfLogins: sql<number>`count(${loginlog.createdAt})`,
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`,
        })
        .from(loginlog)
        .groupBy(loginlog.userId)
        .as("ll")

    const sq = db
        .select({
            id: loginlog.userId,
            numberOfLogins: sql<number>`count(${loginlog.createdAt})`,
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`,
        })
        .from(loginlog)
        .groupBy(loginlog.userId)
        .as("ll")

delightOP•14mo ago

Wow .. that was it ... I totally overlooked the .as('name') . Thank you so much. Now typescript stopped yelling at me ... need to try if the query works as expected - but I guess I'll figure it out from here if something is not working right. Thank you again 👍🏽 Drizzle is simply awesome ❣️ Powerful query builder, that is easy to get used to + fine grained schema definition / migration without extra build steps. The only thing I couldn't figure out yet is how to do a ORDER BY xyz DESC NULLS LAST The common ORDER BY xyz DESC is trivial.

Angelelz•14mo ago

orderBy() method accepts sql magic operator You could do

...
  .orderBy(sql`xyz DESC NULLS LAST`)
...

...
  .orderBy(sql`xyz DESC NULLS LAST`)
...

delightOP•14mo ago

works perfectly, thank you 🙂

delightOP•14mo ago

As stated at https://orm.drizzle.team/docs/sql#sqlt the T in sql<T> has not effect on the resulting type

            numberOfLogins: sql<number>`count(${loginlog.createdAt})`.as('numberOfLogins'),
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`.as('latestLoginDate'),

            numberOfLogins: sql<number>`count(${loginlog.createdAt})`.as('numberOfLogins'),
            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`.as('latestLoginDate'),

only difference is that drizzle can introspect into e.g. (property) numberOfLogins: SQL.Aliased<number> . I wished drizzle would cast/map the types for primitive values like number etc. What is the recomended way to coerce the results to the right types? zod ?

Magical sql operator 🪄 - DrizzleORM

Drizzle ORM | %s

delightOP•14mo ago

for example: As of now we can only get a string representing the count - if we want to do something like: Select count (*) from X in drizzle

Luxaritas•14mo ago

You probably want mapWith https://orm.drizzle.team/docs/sql#sqlmapwith

Magical sql operator 🪄 - DrizzleORM

Drizzle ORM | %s

delightOP•14mo ago

Yes that works ... thank you 👍🙂

            numberOfLogins: sql<number>`count(${loginlog.createdAt})`
                .mapWith(Number)
                .as('numberOfLogins'),

            numberOfLogins: sql<number>`count(${loginlog.createdAt})`
                .mapWith(Number)
                .as('numberOfLogins'),

for count and

            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`
                .mapWith(loginlog.createdAt)
                .as('latestLoginDate'),`

            latestLoginDate: sql<Date>`max(${loginlog.createdAt})`
                .mapWith(loginlog.createdAt)
                .as('latestLoginDate'),`

which seems a little verbose - but ultimately works. Even though somehow it seems to still stringify the Date - at least it looks like it when logging to the console. .mapWith(loginlog.createdAt) doesn't make any difference here ... contrary to the count. Why not use the T - type in sql<T> to map the result type too? > Would make it less verbose. btw. I just double checked in an old prisma project. Prisma infers the correct types from the database even on a prisma.$queryRaw native sql query. For sure that is no reason to go back to it - but I guess that there might be potential to infer types from the query results. So far drizzle seem to somehow stringify all results.

Angelelz•14mo ago

If you use mapWith you don't need to give the type <number> Giving it the type like this <number> is just a type casting, only at compile time mapWith will actually run at runtime the function you pass to it

delightOP•14mo ago

Ahh nice ... this also works with the max(Date) query I used 👍 I see ... I'll try to play with it

Luxaritas•14mo ago

My bet is that this is actually the DB driver, not Drizzle IIRC, if you're using sql , it just passes back whatever the driver gives you Using the mysql2 driver for example, I get a number back from a count And if you provide loginlog.createdAt directly (without the max, etc), without the max, Drizzle should map that itself as long as you've configured the column type correctly

delightOP•14mo ago

I'm using postgres right now ... I can/will also try with pg. Using .mapWith(Number) does the right thing for now.

Luxaritas•14mo ago

Yeah different postgres drivers may have different behaviors That's just one thing to consider when weighing the pros and cons of a driver

delightOP•14mo ago

Yes I think it does ... need to double check ... but in that case I want the latest date only so needs to be max(date)

Luxaritas•14mo ago

Yeah, I know

delightOP•14mo ago

for sure will do as I'm still in a kind of evaluation phase ... but I'm really impressed with drizzle so far.

Luxaritas•14mo ago

This is a situation where drizzle could potentially be smarter if it had its own functions for things like max, count, etc (where it could infer its own mappings)

delightOP•14mo ago

especially schema definition is awesome compared to ... you know I think it is little confusing for a drizzle beginner to have "both" sql<T> and the .mapWith() method. Maybe the docs could mention that .mapWith() is superseeding the T - Type / making it obsolete. Well that is still not correct. Well T is just for TS compiler while mapWith() does the mapping - magic + it somehow sets the typing (at least in my use-cases).

Angelelz•14mo ago

mapWith gives you the types as well just because the TS compiler infers it from the return on the function you pass to it

delightOP•14mo ago

yes I understood this by now ... what I mean it is not obvious by reading the docs

Angelelz•14mo ago

I've been thinking this is something I could add to drizzle later

delightOP•14mo ago

Absolutely, I think that is definitively the right deccission. With the magic sql operator you cover all of those (for the time being)... having dedicated functions for Aggregation etc. is just a nice to have and for sure can be postponed. I guess maybe count might be an exception - not because it is hard to implement with sql but because it is so common that ppl might expect a function for it. But those are just my thoughts + when ppl don't stumble at count () over this they will do just a little bit later - so IMO it is fine the way it is right now. But the docs wasn't that obvious to me at that part + maybe there could be a little more verbose example on .mapWith()

Gaming

Programming

Attempting to get a Joined Subquery query into drizzle crud api