Column 'id' in field list is ambiguous

[aliuygur]

When I run,

$Product = Product::find($id);
$Product->categories()->lists('category_id')

I get below error.

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous (SQL: select `category_id`, `id` from `categories` inner join `category_product` on `categories`.`id` = `category_product`.`category_id` where `category_product`.`product_id` = ?) (Bindings: array ( 0 => '1', ))

Product and Category is many-to-many

[lvivier]

+1 to this. Seems to be the product of the changes to lists here. This will happen if multiple tables use a common column name as their primary key. My current workaround is something like

$product
    ->categories()
    ->getQuery()
    ->getQuery()
    ->lists('category_id');

to get around the Eloquent lists() method but that's pretty gnarly.

[silentworks]

Why not just state the table explicitly? categories.category_id?

[Anahkiasen]

I don't think that works, I try that every time I get that Exception but lists will basically do $model->getAttribute('categories.category_id') and that won't work cause there is no attribute by that name on the model. At least was like that last time I tried.

[parsingeye]

you could do

$product->categories()->select('categories.category_id')->lists('category_id');

[adis-io]

$data = Program::find(Input::get('program'))->aid_types()->lists('name', 'id');

throws exception:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous (SQL: select `name`, `id` from `aid_types` inner join `aid_type_program` on `aid_types`.`id` = `aid_type_program`.`aid_type_id` where `aid_type_program`.`program_id` = ?) (Bindings: array ( 0 => 1, ))

I made it work with @Guilex method

$data = Program::find(Input::get('program'))->aid_types()->select('aid_types.*')->lists('name', 'id');

[eblanshey]

Perhaps this isn't a big issue, but I feel like it would be more intuitive to to be able to do something like this instead of @adisos's solution:

$data = Program::find(Input::get('program'))->aid_types()->lists('name', 'aid_types.id');

[jchamberlain]

As evidenced by the comment at https://github.com/laravel/framework/blob/4.1/src/Illuminate/Database/Query/Builder.php#L1442, removing the alias seems to be official policy, at least for the first param (column). That makes sense for the purpose of array keys in PHP, but definitely makes the queries themselves less intuitive.

[TheOnlyMerlin]

My problem / solution:
Original, not working version:

DB::table('table1')
  ->join('table2','table1.table2ID','=','table2.ID')
  ->join('thisTable', 'table2.thisTableID', '=', 'thisTable.ID')
  ->lists('table1.ID'); // 'table1.' will get stripped, making the call to "id" ambiguous.

I feel like this should work, and would have saved me having to find this page, and figure out a solution.

Here is the workaround version:

DB::table('table1')
  ->select('data.ID') // Add a select so only one column shows up.
  ->join('table2','table1.table2ID','=','table2.ID')
  ->join('thisTable', 'table2.thisTableID', '=', 'thisTable.ID')
  ->lists('ID'); // Now list that one column

Please reconsider merging in the above PR (#4738) from @jchamberlain, or one like it.

[ifch0o1]

My problem details here : http://stackoverflow.com/questions/30843909/laravel-5-eloquent-relation-has-many-through-sql-exeption

When I try to access ORM "Has Many Through" related table I get same Exception - The ID is violation.

$task = $request->user()->tasks()->where('id', $id)->first();
$userInstance->tasks() // working. It returns all tasks. But the where() function fails.

[marvinhosea]

You can do something like this Company::find(1)->customers()->get(['customers.id'])