How to do `UPDATE...SET...FROM` using knex?

user_5228806 0
Viewed 1090

What's the most terse way I can express an UPDATE...SET...FROM SQL statement using knex? This is what I've got currently:

  const query =
    knex('user_subscriptions').update(subscription).toQuery() +
    knex.raw(
      ' from plans p where customer_id = ? and p.id = us.plan_id ' +
        'returning us.*, p.name',
      [customer_id]
    );

The reason I'm doing this is that I want to efficiently return a field from a related table (JOIN style) without needing a separate query.

postgresql-10 knex.js postgresql sql
2 Answers

As instructed in the official site: knexjs.org/#Builder-update

knex('user_subscriptions')
  .returning(['us.*', 'plans.name', 'customer_id'])
  .where({
  customer_id: '?',
  plans.id:  us.plan_id
})
  .update({
    subscription : '?
  })

Does:

update `user_subscriptions` set `subscription ` = '?' where `customer_id` = '?' and 'plans.id' = 'us.plan_id'

Returns:

[ us.*: ..., plans.name: ...,  customer_id: ... ]
user_171896380

why not building all the way raw:

let query = knex.raw(
 'update user_subscriptions '
  + 'set subscription = ?, '
  + 'where p.id = us.plan_id '
  + 'and customer_id = ? '
  + 'returning us.*, p.name '
  , [customer_id]
    );

results = query.rows
user_13674540
Related