Laravel Query Builder join error. SQLSTATE[42000] error

Question

I'm using the Laravel query builder to write a join statement and I find that I'm encountering a strange error. When I run the query below from phpmyadmin, it works but I get an error when I try to access the page in Laravel.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 
'? where `entities`.`deleted_at` is null' at line 1 (SQL: select * from `entities` inner join 
`entity_contact_info` on `entity_contact_info`.`entity_id` = `entities`.`id` and `entities`.`sector_id`
= 2 and `entity_contact_info`.`country_id` IN (select `id` from countries WHERE `region_id` = 9)
where `entities`.`deleted_at` is null)

The query I've built in Laravel is below. Again, when I copy the query from the error above and run it, it works. There seems to be no reason why this isn't working.

$query = Entity::Join("entity_contact_info", function ($join){
                $join->on("entity_contact_info.entity_id", "=", "entities.id")
                    ->where("entities.sector_id", "=", "2")
                    ->where("entity_contact_info.country_id", "IN", "(select `id` from countries WHERE `region_id` = 9)");
                })->get();

Any advice?

score 0 · Answer 1 · answered Jan 19 '16 at 20:52

0

I would say the problem lies in your 2nd where() statement.

Try the following.

->where("entity_contact_info.country_id", "IN", DB::raw("(select `id` from countries WHERE `region_id` = 9)"))

answered Jan 19 '16 at 20:52

user1669496

32,176
9
73
65

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

Try this,

$query = Entity::Join("entity_contact_info", function ($join){
                $join->on("entity_contact_info.entity_id", "=", "entities.id")
                    ->where("entities.sector_id", "=", "2")
                    ->whereIn("entity_contact_info.country_id", DB::raw("(select `id` from countries WHERE `region_id` = 9)"));
                })->get();

Another way,

$query = Entity::Join("entity_contact_info", function ($join) {
                $join->on("entity_contact_info.entity_id", "=", "entities.id")
                ->where("entities.sector_id", "=", "2")
            ->whereIn('entity_contact_info.country_id', function($query) {
                $query->select('id')
                    ->from('countries')
                    ->where('countries.region_id = 9');
            })
        })->get();

Use of where IN laravel

edited May 23 '17 at 10:27

Community

1
1

answered Jan 20 '16 at 04:39

Sagar Naliyapara

3,971
5
41
61

I tried this but I get the following error: Argument 2 passed to Illuminate\Database\Query\JoinClause::whereIn() must be of the type array, object given – Tony Olendo Jan 20 '16 at 07:47
@TonyOlendo which way you have tried first or second? you may try like this `DB::raw("(select id from countries WHERE region_id = 9)")->toArray()` – Sagar Naliyapara Jan 20 '16 at 07:49
Thank you for your continued assistance. I tried both suggestions and your most recent comment generates the following error: Call to undefined method Illuminate\Database\Query\Expression::toArray() – Tony Olendo Jan 20 '16 at 08:57
@TonyOlendo try `DB::raw("(select id from countries WHERE region_id = 9)")->get()->toArray()` – Sagar Naliyapara Jan 20 '16 at 09:05

Laravel Query Builder join error. SQLSTATE[42000] error

2 Answers2