kysely: Update statements with joins don't seem to work

Hello! Just switched from Prisma and loving the library so far. Just ran into an issue where I’m hoping there’s a workaround or I’m doing something wrong.

Basically, I have an existing MySQL statement that reads:

update OverallMV O join OverallMV O2 on O.Rank = O2.Rank and O.UserID <> O2.UserID set O.Tied = true

So I recreated it in Kysely as:

await trx.updateTable("OverallMV as O").innerJoin(
      "OverallMV as O2",
      (join) =>
        join
          .onRef("O.Rank", "=", "O2.Rank")
          .onRef("O.UserID", "<>", "O2.UserID"),
    ).set({ Tied: 1 }).execute();

However, this fails with a ER_PARSE_ERROR since it puts the set before the join:

update `OverallMV` as `O` set `Tied` = 1 inner join `OverallMV` as `O2` on `O`.`Rank` = `O2`.`Rank` and `O`.`UserID` <> `O2`.`UserID`

Any ideas here?

Side note, I’m wondering if this use case was never tested as I would also expect to choose which table to update in the set. So more like .set({ "O.Tied": 1 }), which does not currently work but .set({ Tied: 1 }) does.

About this issue

Original URL
State: open
Created 2 years ago
Comments: 15 (10 by maintainers)

Most upvoted comments

You’re right.

Types are too complicated and its surprising to consumers (join and set switch places in compiled sql because of a from existing or not).

I think expanding .updateTable(...) as follows:

Add an overload that accepts an array of table references to support update table t0, t1, t2, ... syntax.

db.updateTable(['person', 'pet'])
  .set({ 
    'person.status': 'Cat lady', 
    'pet.owner_id': uuid,
  })
  .where('person.id', '=', uuid)
  .where('pet.owner_id', 'is', null)
  .where('pet.species', '=', 'cat')
  .execute()

Add an overload that accepts a 2nd argument - A builder factory with join methods, to support update table t0 inner join t1 ... left join t2 ... syntax.

db.updateTable('person', (jb) => jb.innerJoin('pet', 'pet.owner_id', 'person.id'))
  .set({
    'person.status': 'Proud dog parent',
    'pet.status': 'Happy pup',
  })
  .where('pet.species', '=', 'dog')
  .execute()

Would be aligned with consumer’s expectations of affecting the query’s main clause, and will make “downstream” types consistent and simpler.

igalklebanov on Oct 14, 2022

That wouldn’t work with the types. Correct me if I’m wrong, but on MySQL you can update all joined tables? So we would need to append to UT of UpdateQueryBuilder<DB, UT, TB, O> in “before from joins” and to TB in “after from joins”. We’d need to add a new type argument just to indicate if from has been called.

koskimas on Oct 14, 2022

I think what @igalklebanov suggested regarding WITH is this:

trx
  .with('someGoodName', (qb) => qb
    .selectFrom("OverallMV as O")
    .innerJoin("OverallMV as O2", (join) => join
      .onRef("O.Rank", "=", "O2.Rank")
      .onRef("O.UserID", "<>", "O2.UserID")
    )
    .select('ID')
  )
  .updateTable("OverallMV")
  .whereExists((eb) => eb.selectFrom("someGoodName").select("ID"))
  .set({ Tied: 1 })

koskimas on Oct 12, 2022

It’s ok to add custom methods that don’t exist on other dialects. People don’t call them accidentally (well at least not as easily). But the set method is used on all dialects and if you’d get autocompletion for table.column fields, people would definitely try to use them on all dialects.

koskimas on Oct 12, 2022