Knex.js PostgreSQL column reference "id" is ambiguous

Question

I am trying to select a single item from a collection in my database using knex.js, but getting the error column reference "id" is ambiguous which I assume is from the id property on the select table and the join table.

trying to accomplish populating a single menu item that references an inventory item.

I have a menu_items table the holds references to a priceId, itemId, and a menuId.

the itemId references inventory items, that hold the relevant information thats needed for the menu item.

right now I have this query.

async function getMenuItem({ menuItemId }) {
  let menuItem = await db('menu_items')
    .where({ id: menuItemId })
    .first()

  return menuItem ? menuItem : null
}

which produces this object

{ 
 id: '06e0472c-f805-4dc7-bdd4-867e823e44f6',
 menuId: '99cca00e-cb43-4423-ab6c-2ed01a329e15',
 itemId: '94ff9e9b-9fec-4026-b735-b326f3f3b88f',
 priceId: 'be5a9e89-f04c-4320-8049-2087179732c2',
 created_at: 2019-10-12T21:09:55.394Z,
 updated_at: 2019-10-12T21:09:55.394Z 
}

I've tried this query

async function getMenuItem({ menuItemId }) {
  let menuItem = await db('menu_items')
    .where({ id: menuItemId })
    .leftJoin('items', 'menu_items.itemId', 'items.id')
    .first()

  return menuItem ? menuItem : null
}

tryin the following snippet produces the same error

  const menuItem = await db
    .select(
      'id as m_id',
      'menuId',
      'priceId',
      'itemId',
    )
    .from('menu_items')
    .where({ id: menuItemId })
    .leftJoin('items', 'menu_items.itemId', 'items.id')

I expected the output to show this single menu item, I could reallllllllly use some guidance here or suggestions on how to get this working properly.

Answer 1

You're not specifying which id in your where clause. Try writing it as:

async function getMenuItem({ menuItemId }) {
  let menuItem = await db('menu_items')
    .where({ 'menu_items.id': menuItemId })
    .leftJoin('items', 'menu_items.itemId', 'items.id')
    .first()

  return menuItem ? menuItem : null
}

Because both menu_items and items have a column named id, you should specify which id you are referring to.