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.
我正在使用Laravel查询构建器编写连接语句,我发现我遇到了一个奇怪的错误。当我从phpmyadmin运行下面的查询时,它可以工作,但是当我尝试访问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.
我在Laravel中构建的查询如下。同样,当我从上面的错误中复制查询并运行它时,它可以工作。似乎没有理由不这样做。
$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?
任何建议?
2 个解决方案
#1
0
I would say the problem lies in your 2nd where()
statement.
我会说问题出在你的第二个where()语句中。
Try the following.
请尝试以下方法。
->where("entity_contact_info.country_id", "IN", DB::raw("(select `id` from countries WHERE `region_id` = 9)"))
#2
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();
使用IN laravel
#1
0
I would say the problem lies in your 2nd where()
statement.
我会说问题出在你的第二个where()语句中。
Try the following.
请尝试以下方法。
->where("entity_contact_info.country_id", "IN", DB::raw("(select `id` from countries WHERE `region_id` = 9)"))
#2
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();
使用IN laravel