Advanced aggregations help

Here's a Drizzle query that I'm trying to do :

      const videoLogs = db
        .select({
          id: S.videoLog.id,
          chapterId: S.videoLog.chapterId,
          courseId: S.videoLog.courseId,
          watchedUntil: S.videoLog.watchedUntil,
          videoId: S.videoLog.videoId,
          completedVideos: sql`(
            select count(*)
            from ${S.vidStat}
            where ${S.vidStat.userId} = ${userId} 
            and ${S.vidStat.completedWatching} = true
            and ${S.vidStat.chapterId} = ${S.videoLog.chapterId}
          )`.mapWith(Number)
        })
        .from(S.videoLog)
        .where(eq(S.videoLog.userId, userId))
        .as('videoLogs');

      const videoLogs = db
        .select({
          id: S.videoLog.id,
          chapterId: S.videoLog.chapterId,
          courseId: S.videoLog.courseId,
          watchedUntil: S.videoLog.watchedUntil,
          videoId: S.videoLog.videoId,
          completedVideos: sql`(
            select count(*)
            from ${S.vidStat}
            where ${S.vidStat.userId} = ${userId} 
            and ${S.vidStat.completedWatching} = true
            and ${S.vidStat.chapterId} = ${S.videoLog.chapterId}
          )`.mapWith(Number)
        })
        .from(S.videoLog)
        .where(eq(S.videoLog.userId, userId))
        .as('videoLogs');

Unfortunately I'm getting a drizzle type error for the count one. What am I doing wrong ?

21 Replies

HebiliciousOP•11mo ago

Oh nvm, need to use .as on the aggregate field.

Angelelz•11mo ago

This would be a lot easier if this PR is approved: https://github.com/drizzle-team/drizzle-orm/pull/1674

GitHub

Feat: Allow subqueries in select fields by Angelelz · Pull Request ...

close #361 [All] Implemented the types inference for subqueries in SelectFields [All] Implemented type tests [All] Implemented correct handling of subqueries in selected fields [All] Added integra...

HebiliciousOP•11mo ago

Oh yeah that loooks nice! Does that means here I could replace the sql` with a db.select ? @Angelelz in your example I'm not sure what happens to the count ... does the population just grab the first field of the db.select return object?

Angelelz•11mo ago

You know, now that I think about this, can you try adjusting your query to:

const sq = db
  .select({ count: count().as('count') })
  .from(S.vidStat)
  .where(and(
    eq(S.vidStat.userId, userId),
    eq(S.vidStat.completedWatching, true),
    eq(S.vidStat.chapterId, S.videoLog.chapterId)
  ))
  .as('sq')
const videoLogs = db
        .select({
          id: S.videoLog.id,
          chapterId: S.videoLog.chapterId,
          courseId: S.videoLog.courseId,
          watchedUntil: S.videoLog.watchedUntil,
          videoId: S.videoLog.videoId,
          completedVideos: sq.count
        })
        .from(S.videoLog)
        .where(eq(S.videoLog.userId, userId))
        .as('videoLogs');

const sq = db
  .select({ count: count().as('count') })
  .from(S.vidStat)
  .where(and(
    eq(S.vidStat.userId, userId),
    eq(S.vidStat.completedWatching, true),
    eq(S.vidStat.chapterId, S.videoLog.chapterId)
  ))
  .as('sq')
const videoLogs = db
        .select({
          id: S.videoLog.id,
          chapterId: S.videoLog.chapterId,
          courseId: S.videoLog.courseId,
          watchedUntil: S.videoLog.watchedUntil,
          videoId: S.videoLog.videoId,
          completedVideos: sq.count
        })
        .from(S.videoLog)
        .where(eq(S.videoLog.userId, userId))
        .as('videoLogs');

I would only like to see if that works In particular, please pay attention to the completedVideos type at runtime. please To answer your question, yes. That's how you'd write that query in raw sql. in sql it just grab the only column you have, that should only return one row two.

HebiliciousOP•11mo ago

I will let you know if that works, gimme some time, I've put a note in my source file 😄 I would love that feature, but I think that's not very intuitive that it automatically unwraps something

Angelelz•11mo ago

For people that knows Sql it might be, I guess the team would have to approve the API If you see the original issue, you'll see some examples

HebiliciousOP•11mo ago

const res = await db.select({
    population: db.select({ count: count().as('population') }).from(users).where(
        eq(users.cityId, cities.id),
    ),
    name: cities.name,
}).from(cities);

const res = await db.select({
    population: db.select({ count: count().as('population') }).from(users).where(
        eq(users.cityId, cities.id),
    ),
    name: cities.name,
}).from(cities);

Would that be possible instead?

Angelelz•11mo ago

Actully, it should be intuitive for you because that's exactly how you wrote it initially I believe I tried to make it work like this but I would have needed to change unrelated parts of the code to make the .as() not necessary

HebiliciousOP•11mo ago

yeah its intuitive with the sql syntax because I have one single thing in select, so I expect this to be returned. But with the query builder syntax I expect an object with multiple fields.

Angelelz•11mo ago

Now remember, it dosn't really matter what you alias it as, at the end it will get mapped to the key you provided

HebiliciousOP•11mo ago

yeah. maybe if you had .first() or something it would be more intuitive. Still a neat addition though.

Angelelz•11mo ago

I think I made it throw a type error if you selected more than one item

HebiliciousOP•11mo ago

or having to call .count at the end

Angelelz•11mo ago

calling count is what I suggested you do here I don't really know if that works, I believe it does

HebiliciousOP•11mo ago

yeah I love that suggestion, let me see if I can check if that works soon. I don't have any data now, just rewriting some prisma queries with drizzle. it works type wise though, will let uknow at runtime, thanks I might make a doc PR with this though, it was hard to figure out. oh and also would like this in the doc onConflictDoUpdate({ target: [col1, col2]} ) Will have the time to give you a report next week because of the holidays @Angelelz Haven't forgotten 😄

HebiliciousOP•11mo ago

hopefully you can get this merged soon https://github.com/drizzle-team/drizzle-orm/pull/1659

GitHub

[Pg] Fix: all datetime mappings by Angelelz · Pull Request #1659 · ...

This PR will close #806, close #971, close #1176, close #1185, close #1407 and close #1587. Most are the same. The problem is postgres.js, it didn't have a clear way of replace the default pars...

HebiliciousOP•11mo ago

@Angelelz hey, just reporting back, it doesn't work :/ error while getting watch history column "count" does not exist the raw sql works tho

Angelelz•11mo ago

Yeah, I think that you're hitting the qualifier issue

Angelelz•11mo ago

https://github.com/drizzle-team/drizzle-orm/issues/1242

GitHub

[BUG]: Selecting from sub-query does not qualify the field · Issue...

What version of drizzle-orm are you using? 0.28.6 What version of drizzle-kit are you using? n/a Describe the Bug I have this query / subquery const aggQuery = db .select({ book: booksSubjects.book...

Angelelz•11mo ago

I opened a PR for that one as well

HebiliciousOP•11mo ago

Fantastic job man, I hope your work will start getting merged soon :D. TBH the sql`` syntax looks fine to me, so I'm really looking forward to this one https://github.com/drizzle-team/drizzle-orm/pull/1674

Gaming

Programming

Advanced aggregations help