SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

Question

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

Mollick Aug '16

Created Aug '16	Last Reply Aug '16	Replies 1	Views 581	Votes 0

Mollick 665

Aug '16

I want to run the below code but it shows the error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous

Code:

$query = $obj->modelsManager->createBuilder() ->columns([ 'id' => 'Exams.id', 'name' => 'Exams.name', 'time' => 'Exams.time', 'course' => 'Courses.name', 'description' => 'Courses.description', 'information' => 'Courses.information', 'courseCode' => 'Courses.code', 'logo' => 'Vendors.logo', 'vendor' => 'Vendors.name' ]) ->from('Exams') ->join('Courses', 'Exams.course_id = Courses.id') ->join('Vendors', 'Exams.vendor_id = Vendors.id') ->where('Vendors.active = :vactive: AND Courses.active = :cactive: AND Exams.active = :eactive: AND Vendors.name = :vendor: AND Courses.name like :course: AND Exams.name like :exam:', ['vactive' => 1, 'cactive' => 1, 'eactive' => 1, 'vendor' => 'aaa', 'course' => 'bbb', 'exam' => 'ccc']);

$query->getQuery()->execute();

As far I understand I am using table aliases for every name column in where clause. And also use active column in same way but it doesn't have any issue/error.

Nikolay Mihaylov · Answer 1 · 2016-08-04T23:58:23-07:00

Hmm can you try with:

->from(['Exams' => 'Exams'])

Or I rewrote and reformated your query:

$query = $obj->modelsManager->createBuilder()
    ->columns([
        'id' => 'e.id', 
        'name' => 'e.name', 
        'time' => 'e.time', 
        'course' => 'c.name', 
        'description' => 'c.description', 
        'information' => 'c.information', 
        'courseCode' => 'c.code', 
        'logo' => 'v.logo', 
        'vendor' => 'v.name'
    ])
    ->from(['e' => 'Exams'])
    ->join('Courses', 'e.course_id = c.id')
    ->join('Vendors', 'e.vendor_id = v.id')
    ->where('v.active = :vactive:', ['vactive' => 1])
    ->andWhere('c.active = :cactive:', ['cactive' => 1])
    ->andWhere('e.active = :eactive:', ['eactive' => 1])
    ->andWhere('v.name = :vendor: ', ['vendor' => 'aaa'])
    ->andWhere('c.name like :course:', ['course' => 'bbb'])
    ->andWhere('e.name like :exam:', ['exam' => 'ccc']);

$query->getQuery()->execute();

Mollick · Answer 2 · 2016-08-05T02:39:36-07:00

Same thing. No change.

Hmm can you try with:

->from(['Exams' => 'Exams'])

Or I rewrote and reformated your query:

$query = $obj->modelsManager->createBuilder()
   ->columns([
       'id' => 'e.id', 
       'name' => 'e.name', 
       'time' => 'e.time', 
       'course' => 'c.name', 
       'description' => 'c.description', 
       'information' => 'c.information', 
       'courseCode' => 'c.code', 
       'logo' => 'v.logo', 
       'vendor' => 'v.name'
   ])
   ->from(['e' => 'Exams'])
   ->join('Courses', 'e.course_id = c.id')
   ->join('Vendors', 'e.vendor_id = v.id')
   ->where('v.active = :vactive:', ['vactive' => 1])
   ->andWhere('c.active = :cactive:', ['cactive' => 1])
   ->andWhere('e.active = :eactive:', ['eactive' => 1])
   ->andWhere('v.name = :vendor: ', ['vendor' => 'aaa'])
   ->andWhere('c.name like :course:', ['course' => 'bbb'])
   ->andWhere('e.name like :exam:', ['exam' => 'ccc']);

$query->getQuery()->execute();