CODEWITHSUNDEEP
databasepostgresqlknex.js
Stefano Giraldi
Stefano Giraldi

Reputation: 1309

Knex Id Column overwritten by joined table id

I have a REST API endpoint that returns an inner join recordset defined like this:

const getByRecipeId = recipeId => {
  return knex("recipe_details")
    .innerJoin("jobs", "jobs.id", recipe_details.id_job")
    .where("id_recipe", recipeId)
}

In debug I have the following sql statement (Postgres):

select
    *
from
    "recipe_details"
inner join "jobs" on
    "jobs"."id" = "recipe_details"."id_job"
where
    "id_recipe" = 1

That returns this recordset

id|id_recipe|seq|id_job|qty_time|qty_job|id|code|description|
--|---------|---|------|--------|-------|--|----|-----------|
 1|        1| 10|     1|      10|     24| 1|job1|job descr1 |
 3|        1| 30|     2|      15|     24| 2|job2|job descr2 |
13|        1| 50|     3|      50|     15| 3|job3|job descr3 |
 2|        1| 20|     3|       5|     30| 3|job3|job descr3 |
 4|        1| 40|     3|      25|     24| 3|job3|job descr3 |

As you can see, there are two id fields, one for the recipe_details table and one for the jobs table.

The problem is that the javascript object returned by the endpoint has only one id property that is not of the main table but is overwritten by the last id field returned by the SQL statement.

How can I avoid this behavior? Thank you for your help

Upvotes: 3

Views: 1235

Answers (1)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

This happens because of how node-pg driver behaves. When one joins in more columns to row, joined columns with the same column name always overwrite the earlier ones in select results.

You can workaround this by telling explicitly which columns to select for the result row.

Like: 

knex("recipe_details")
    .select("recipe_details.*", "jobs.code", "jobs.description")
    .innerJoin("jobs", "jobs.id", recipe_details.id_job")
    .where("id_recipe", recipeId)

Upvotes: 3

Related Questions