CODEWITHSUNDEEP
javascriptpostgresqlknex.js
Jesus Favela
Jesus Favela

Reputation: 19

why my query is ambiguous? Knex and Postgres

I'm building a query, but when execute it trows this error:

error: column reference "id" is ambiguous

I don't know why, because I'm referencing the table that contains the id

let activeComplaints = await knex.select(fieldActiveComplaints)
  .from('mailbox_complaints')
  .innerJoin('superheroes', 'superheroes.id', 'mailbox_complaints.superheroe_id')
  .where('superheroes.id', '=', 'mailbox_complaints.id')
  .orderBy('mailbox_complaints.id', 'desc')
  .limit(pageSize)
  .offset(offset)

let count = knex.count()
  .from('superheroes')
  .innerJoin('superheroes', 'superheroes.id', 'mailbox_complaints.superheroe_id')
  .where('superheroes.id', '=', 'mailbox_complaints.id')
  .then(([query]) => parseInt(query.count, 10))
console.log('activeComplaints==>', activeComplaints)
return Promise.all([activeComplaints, count])

And my fieldActiveComplaints looks like this

const fieldActiveComplaints = [
'id',
'superheroes.name',
'commentary',
'created_date',
]

Upvotes: 0

Views: 506

Answers (1)

mu is too short
mu is too short

Reputation: 434635

Your select uses fieldActiveComplaints which looks like this:

const fieldActiveComplaints = [
  'id',
  ...

There's your unqualified and ambiguous id. You have id columns in both superheroes and mailbox_complaints and your joins make them both available to the SELECT clause, PostgreSQL is telling you that it doesn't know which id column you want to SELECT.

Qualify everything in fieldActiveComplaints:

const fieldActiveComplaints = [
  'mailbox_complaints.id',
  'superheroes.name',
  'mailbox_complaints.commentary',
  'mailbox_complaints.created_date',
]

so that there's no ambiguity.

Upvotes: 2

Related Questions