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?

score 1 · Accepted Answer · answered Jul 30 '19 at 14:01

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;
  );

answered Jul 30 '19 at 14:01

technogeek1995

3,185
2
31
52

Thanks, however when I run that I get this error `TypeError: (intermediate value) is not iterable` Does that mean anything to you? – runnerpaul Jul 30 '19 at 14:51
1

Try to remove `.first()`? – technogeek1995 Jul 30 '19 at 14:52
1

Perfect. That's exacxtly what I'm after. – runnerpaul Jul 30 '19 at 14:54

score 0 · Answer 2 · answered Sep 16 '22 at 07:27

Other way is using returning('*') like below code

update = async (noteId, text, db) => {
  const result = await db
    .knex("notes")
    .where("id", noteId)
    .update({
      timestamp: new Date(),
      note: text,
    })
    .returning("*");
    return result?.length ? result[0] : null;
};

Thanks and Hope that help you

Return data from joined table in Knex update

2 Answers2