Updating with a subquery

Can I do this in Drizzle without the raw SQL?

  db.execute(sql`
      UPDATE feeds AS f
        SET "lastPublishedAt" = (
          SELECT MAX(fi."publishedAt")
          FROM "feedItems" AS fi
          WHERE fi."feedId" = f.id
        );`);

  db.execute(sql`
      UPDATE feeds AS f
        SET "lastPublishedAt" = (
          SELECT MAX(fi."publishedAt")
          FROM "feedItems" AS fi
          WHERE fi."feedId" = f.id
        );`);

21 Replies

Angelelz•14mo ago

The problem is update <table as t> I don't think drizzle currently have a way to do table alias with as

Luxaritas•14mo ago

You can, there’s an alias() function

Angelelz•14mo ago

Only in the subquery you could use drizzle syntax

joostschuur•14mo ago

Yeah, I saw some .as() queries, but not with update. Still new to Drizzle and it was easy enough to do a raw SQL query, so I didn't dig into it too much.

Angelelz•14mo ago

I just tested the alias function, it doesn't work properly inside the update

const us = alias(users, "us");

    const qb = db
        .update(us)
        .set({
            createdAt: db
                .select({ max: sql`max(created_at)` })
                .from(po)
                .where(eq(po.authorId, us.id))
                .getSQL(),
        })
        .toSQL();

const us = alias(users, "us");

    const qb = db
        .update(us)
        .set({
            createdAt: db
                .select({ max: sql`max(created_at)` })
                .from(po)
                .where(eq(po.authorId, us.id))
                .getSQL(),
        })
        .toSQL();

joostschuur•14mo ago

Ah, that should help me write my own, thansk

Luxaritas•14mo ago

I’m not sure that the alias should be necessary here, fwiw

Angelelz•14mo ago

this produces: 'update us set created_at = select max(created_at) from posts po where po.author_id = us.id'

joostschuur•14mo ago

Will give this a shot shortly

Angelelz•14mo ago

Don't use my example, doesn't work

Luxaritas•14mo ago

I think the canonical way to do this would be UPDATE FROM, right? Which drizzle doesn’t support yet https://github.com/drizzle-team/drizzle-orm/issues/208

GitHub

Support UPDATE FROM · Issue #208 · drizzle-team/drizzle-orm

Angelelz•14mo ago

What i'm trying to say is that the alias doesn't get aliased in the update for some reason I've never used update from But updating with a subquery from another table is what he's after

joostschuur•14mo ago

as() doesn't help? https://orm.drizzle.team/docs/crud#with-clause

Querying with SQL-like syntax [CRUD] – DrizzleORM

Drizzle ORM | %s

Luxaritas•14mo ago

Yeah there’s missing handling for that, just checked

joostschuur•14mo ago

So, if I were to file a feature request GH issue for this, what's a good title for it? '[FEATURE]: Support aliases for update queries' ?

Angelelz•14mo ago

This is what I was kinda able to do

    const po = alias(posts, "po");
    const updated = await db.execute(
        sql`update users us set created_at = (${db
            .select({ max: sql`max(created_at)` })
            .from(po)
            .where(eq(po.authorId, sql`us.id`))
            .getSQL()})`,
    );

    const po = alias(posts, "po");
    const updated = await db.execute(
        sql`update users us set created_at = (${db
            .select({ max: sql`max(created_at)` })
            .from(po)
            .where(eq(po.authorId, sql`us.id`))
            .getSQL()})`,
    );

Which is not a great improvement over what you had But it's working

Luxaritas•14mo ago

Why is the alias needed at all?

joostschuur•14mo ago

I did try it without the alias Honestly, at this point I changed my mind about updating them all at once, and want to target each feed individually anyway, due to the way each feed can time out and run errors Honestly, I just can't wrap my head around the Drizzle syntax

Luxaritas•14mo ago

FYI, this seems like it should work:

db.update(feeds)
  .set({
    lastPublishedAt: sql`${
      db.select({ max: sql`MAX(${feedItems.publishedAt })`})
        .from(feedItems)
        .where(eq(feedItems.id, feeds.id))
    }`
  })

db.update(feeds)
  .set({
    lastPublishedAt: sql`${
      db.select({ max: sql`MAX(${feedItems.publishedAt })`})
        .from(feedItems)
        .where(eq(feedItems.id, feeds.id))
    }`
  })

As far as drizzle syntax goes, it almost 1:1 maps to raw SQL. In this case, the notable gotchas would be: - You can't currently use an alias for the table you're updating (bug/unimplemented) - but you also don't need it, it just adds extra work since in drizzle, you want to be using the actual table variables anyways, and only alias when you need to differentiate - You have to wrap the subquery in a sql template string, as set doesn't currently support a subquery directly - You have to name the field in the select (this winds up being how it would map back to a JS object, even though that's unused in this case) - There isn't a builtin for MAX, so again you need a sql template string

joostschuur•14mo ago

Almost. Just needed a tweak: .where(eq(feedItems.feedId, feeds.id)) Thanks for writing this up, especially with the detailed notes! I've been using Prisma for so long, my SQL skills have atrophied 🙂 Naming the field max, but still having lastPublishedAt: work like that without a .max definitely looks counterintuitive for a novice to the API like it. I also hadn't thought about putting a whole db... chain into a sql call like that, especially without it ending in a .toSQL(). But it works 🙂

Luxaritas•14mo ago

Yeah, they’re workarounds for current limitations, so hopefully will become more intuitive over time

Gaming

Programming

Updating with a subquery