delight Posts - Answer Overflow

delight

Explore posts from servers

•Created by delight on 10/2/2023 in #help

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 ?

36 replies

DTDrizzle Team

•Created by delight on 9/30/2023 in #help

Drizzle Studio on postgres

While - drizzle-kit generate:pg - drizzle-kit push:pg - drizzle-kit introspect:pg all work also with the postgres driver ... (driver: 'pg' in drizzle.config.ts) - drizzle-kit studio does not and needs additionally the pg package (node-postgres) installed as dependency. Is that something I should open an issue for on github ?

9 replies

DTDrizzle Team

•Created by delight on 9/27/2023 in #help

Am I doing it right ?

1) I wanted to get the native sql query result typed. I used the following code.

type LimitedBoardDataVO = Pick<typeof noticeBoard.$inferSelect, 'id' | 'title' | 'content'>

export const getDrizzleSQLNoticeboard = () => {
    return db.execute(
        sql`select "id", "title", "content" from "notice_board" "noticeBoard" order by "noticeBoard"."createdAt" desc limit ${1}`
    ) as Promise<LimitedBoardDataVO[]>
}

type LimitedBoardDataVO = Pick<typeof noticeBoard.$inferSelect, 'id' | 'title' | 'content'>

export const getDrizzleSQLNoticeboard = () => {
    return db.execute(
        sql`select "id", "title", "content" from "notice_board" "noticeBoard" order by "noticeBoard"."createdAt" desc limit ${1}`
    ) as Promise<LimitedBoardDataVO[]>
}

which works fine in general. I was just wondering if there is another way with e.g. generics sql<LimitedBoardDataVO> or so ? 1b) Is there an easy / better way to get ts-types from the schema? Should I have to use things like drizzle-zod (haven't looked into)? Seems a little overkill if you don't want to do validation. So is there a better way to access / generate those types from the schema definitions ? 2) I implemented an app (SvelteKit stack against postgres / neondb) with drizzle, kysely and prisma. Just to compare DX, Migrations and Performance. I added drizzle in 2 connection configurations once with postgres and once with pg. I noticed that the pg connection dies quicker then the postgres connection ( at least that is what I think it does as the first query takes a little longer after a short time - around 500ms vs 50ms ). Is is just a thing of configuring the expiration-time of the connections? Is it even possible? I noticed that the connections of the rather slow prisma connection lives the longest. postgres seems to live a little longer then (node-)pg. 3) kyzely facilitates an easy possibility to log query duration additionally to the query itself. Is there something similar in drizzle?

13 replies

Gaming

Programming