Return data from joined table in Knex update

Question

I have this knex update:

update = async (noteId, text, db) => (
    db.knex('notes')
      .returning([
        'id',
        'note',
        'user_id',
        'product_id',
        'timestamp',
      ])
      .where('id', noteId)
      .update({
        timestamp: new Date(),
        note: text,
      })
  );

In the returned data I wish to include product_name. This has to come from a product table which is joined on notes.product_id = product.id. How do I do this through knex?

Answer 1

Knex is a SQL query builder. It's not an ORM. If you're looking for eager loading, you should check out an ORM like ObjectionJS.

To do this with Knex, you would do it the same as you do in SQL. The simplest way is to perform the update. Then, you'll need to perform a select and knex's inner join.

update = async (noteId, text, db) => (
    await db.knex('notes')
      .where('id', noteId)
      .update({
        timestamp: new Date(),
        note: text,
      });

    const response = await db.knex('notes')
      .where('id', noteId)
      .innerJoin('products', 'notes.product_id', 'products.id')
      .first();

    return response;
  );