CODEWITHSUNDEEP
javascriptnode.jsknex.js
runnerpaul
runnerpaul

Reputation: 7196

Return data from joined table in Knex update

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?

Upvotes: 1

Views: 1331

Answers (2)

tuanngocptn
tuanngocptn

Reputation: 1471

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

Upvotes: 0

technogeek1995
technogeek1995

Reputation: 3444

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

Upvotes: 1

Related Questions