DT
Drizzle Team•12mo ago
delight

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
Luxaritas•12mo 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
kratious•12mo 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")
delight
delight•12mo 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
Angelelz•12mo ago
orderBy() method accepts sql magic operator You could do 
...
  .orderBy(sql`xyz DESC NULLS LAST`)
...
...
  .orderBy(sql`xyz DESC NULLS LAST`)
...
delight
delight•12mo ago
works perfectly, thank you 🙂
delight
delight•12mo 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
delight
delight•12mo 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
Luxaritas•12mo ago
delight
delight•12mo 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
Angelelz•12mo 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
delight
delight•12mo ago
Ahh nice ... this also works with the max(Date) query I used 👍 I see ... I'll try to play with it
Luxaritas
Luxaritas•12mo 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
delight
delight•12mo ago
I'm using postgres right now ... I can/will also try with pg. Using .mapWith(Number) does the right thing for now.
Luxaritas
Luxaritas•12mo 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
delight
delight•12mo 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)
Want results from more Discord servers?
Add your server