在模型和相关模型的领域中查询，使用或其中的多个 [英] Multiple where queries, using orWhere, in fields of model and related models
本文介绍了在模型和相关模型的领域中查询，使用或其中的多个的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试添加一个功能，可以在过滤数据时使用逗号作为OR分隔符。我需要做一般，因为我有相当多的表，领域和关系。另外一些表是动态构建的。
添加模型自身字段的where子句的代码是：
foreach（$ filters as $ column => $ filter）{
$ q-> where（function（$ q）use（$ filter，$ model，$列）{
$ first = array_pop（$ filter）;
$ q-> where（$ model。$ column，'LIKE'，％$ first％）;
foreach（$ filter as $ or）{
$ q-> orWhere（$ model。$ column，'LIKE'，'％$ or％）;
}
}）;
}
这样可以预期。当尝试与关系领域做同样的事情时，唯一的区别是使用where和HAS并包含模型：
foreach（$ filters作为$ column => $ filter）{
$ q-> whereHas（$ model，function（$ q）use（$ filter，$ model，$ column）{
$ first = array_pop $ filter $;
$ q-> where（$ model。$ column，'LIKE'，％$ first％）;
foreach（$ filter as $ or）{
$ q-> orWhere（$ model。$ column，'LIKE'，％$或％）;
}
}）;
}
问题是如果我对关系字段进行过滤，它不起作用如预期。我怀疑它与哪里有一个sub / orWhere子句。这是我得到的输出
没有过滤器：
| ------------ + ----------------------------------- + ------------- ------------------------- |
|联系人ID |类型（联系SQL表中的字段）| Ecozone（生态区SQL表）|
| ------------ + -------------------------------- --- + -------------------------------------- |
|过滤器 - > | | |
| ------------ + -------------------------------- --- + -------------------------------------- |
| 1 |经理|布什|
| 2 | |森林|
| 3 |工人| |
| ------------ + -------------------------------- --- + -------------------------------------- |
当前模型的单个过滤器（正确）：
| ------------ + --------------- -------------------- + ----------------------------- --------- |
|联系人ID |类型（联系SQL表中的字段）| Ecozone（生态区SQL表）|
| ------------ + -------------------------------- --- + -------------------------------------- |
|过滤器 - > |男人| |
| ------------ + -------------------------------- --- + -------------------------------------- |
| 1 |经理|布什|
| ------------ + -------------------------------- --- + -------------------------------------- |
当前模型的多个过滤器（正确）：
| ------------ + --------------- -------------------- + ----------------------------- --------- |
|联系人ID |类型（联系SQL表中的字段）| Ecozone（生态区SQL表）|
| ------------ + -------------------------------- --- + -------------------------------------- |
|过滤器 - > |男人， |
| ------------ + -------------------------------- --- + -------------------------------------- |
| 1 |经理|布什|
| 3 |工人| |
| ------------ + -------------------------------- --- + -------------------------------------- |
相关模型字段上的单个过滤器（正确）：
| ------------ + -------------- --------------------- + ---------------------------- ---------- |
|联系人ID |类型（联系SQL表中的字段）| Ecozone（生态区SQL表）|
| ------------ + -------------------------------- --- + -------------------------------------- |
|过滤器 - > | |巴士|
| ------------ + -------------------------------- --- + -------------------------------------- |
| 1 |经理|布什|
| ------------ + -------------------------------- --- + -------------------------------------- |
相关模型字段上的多个过滤器（不正确）：
| ------------ + -------------- --------------------- + ---------------------------- ---------- |
|联系人ID |类型（联系SQL表中的字段）| Ecozone（生态区SQL表）|
| ------------ + -------------------------------- --- + -------------------------------------- |
|过滤器 - > | |巴士，为|
| ------------ + -------------------------------- --- + -------------------------------------- |
| 1 |经理|布什|
| 2 | |森林|
| 3 |工人| |
| ------------ + -------------------------------- --- + -------------------------------------- |
整个代码路径如下：
在控制器中：
返回联系人:: with（
'ecozone'
） - > where （$ q）{
$ this-> set_filters（$ q，'contact'）;
}） - > paginate（$ count）;
在BaseController中：
protected function set_filters（$ q，$ current_model）{
$ filters_array = $ this-> parse_filters（）;
if（$ filters_array）{
foreach（$ filters_array as $ model => $ filters）{
if（$ model == $ current_model）{
foreach（$ filters as $ column => $ filter）{
$ q-> where（function（$ q）use（$ filter，$ model，$ column）{
$ first = array_pop （$ filter $）$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
$ q-> orWhere（$ model。$ column，'LIKE'，％$或％）;
}
}）;
}
} else {
foreach（$ filters as $ column => $ filter）{
$ q-> whereHas（$ model，function（$ q）use （$ filter，$ model，$ column）{
$ first = array_pop（$ filter）;
$ q-> where（$ model。$ column，'LIKE'，％$第一个％）;
foreach（$ filter as $ or）{
$ q-> orWhere（$ model。$ column，'LIKE'，％$ or％）;
}
}）;
}
}
}
}
}
$ this-> filters是：
protected function parse_filters（）{
$ filters = ：获得（ '过滤器'）;
$ filters = json_decode（$ filters）;
$ filters = array_where（$ filters，function（$ key，$ value）{
return！empty（$ value）;
}）;
$ filters =（Array）$ filters;
$ has_filters =！empty（$ filters）;
if（$ has_filters）{
$ filters_array = [];
foreach（$ filters as $ key => $ value）{
$ value = explode（'，'，$ value）;
array_set（$ filters_array，$ key，$ value）;
}
} else {
$ filters_array = false;
}
return $ filters_array;
}
并返回，我认为是正确的，以下数组。第一级是模型，第二是字段，第三是逗号分隔OR子句
Array（
[ecozone] => ; Array
（
[ecozone] => Array
（
[0] => bush
[1] => forest
）
）
[contact] => Array
（
[contact_type] => Array
（
[ 0] => manager
[1] => worker
）
）
）
过滤当前模型字段时使用的SQL的转储。这样可以预期：
array（size = 4）
0 =>
array（size = 3）
'query'=>字符串'select count（*）作为聚合从`contact`，其中`contact`.`deleted_at`是null和（（`contact`.`physical_address` LIKE？或`contact`.`physical_address` LIKE？））'（length = 163）
'bindings'=>
数组（size = 2）
0 =>字符串'％add％'（length = 5）
1 =>字符串'％nana％'（length = 6）
'time'=>浮动1.67
1 =>
array（size = 3）
'query'=> string'select * from`contact`其中`contact`.`deleted_at`是null和（（`contact`.`physical_address` LIKE？或`contact`.`physical_address` LIKE？））limit 10 offset 0'（length = 161）
'bindings'=>
数组（size = 2）
0 =>字符串'％add％'（length = 5）
1 =>字符串'％nana％'（length = 6）
'time'=> float 0.91
2 =>
array（size = 3）
'query'=> stringselect * from`ecozone`其中`ecozone`.`deleted_at`为null，`ecozone`.`id`（？
数组（size = 2）
0 =>字符串'1'（长度= 1）
1 => string'2'（length = 1）
'time'=> float 0.8
由相关模型字段过滤的SQL查询转储。这不按预期方式工作：
array（size = 4）
0 =>
array（size = 3）
'query'=>字符串'select count（*）作为聚合从`contact`，其中`contact`.`deleted_at`为null，（（select select（*）from`ecozone`，`ecozone`.`deleted_at`为null和`contact`。 `ecozone_id` =`ecozone`.`id`和`ecozone`.`ecozone` LIKE？或`ecozone`.`ecozone` LIKE？和`ecozone`.`deleted_at`是null）> = 1）'（length = 301）
'bindings'=>
数组（size = 2）
0 =>字符串'％grass％'（length = 7）
1 =>字符串'％bush％'（length = 6）
'time'=> float 1.18
1 =>
array（size = 3）
'query'=> string'select * from`contact`其中`contact`.`deleted_at`为null，（（select select（*）from`ecozone` where`ecozone`.`deleted_at`为null，`contact`.`ecozone_id` =` ecozone`.`id`和`ecozone`.`ecozone` LIKE？或`ecozone`.`ecozone` LIKE？和`ecozone`.`deleted_at`是null）> = 1）limit 10 offset 0'（length = 299）
'bindings'=>
数组（size = 2）
0 =>字符串'％grass％'（length = 7）
1 =>字符串'％bush％'（length = 6）
'time'=> float 0.89
2 =>
array（size = 3）
'query'=> stringselect * from`ecozone`其中`ecozone`.`deleted_at`为null，`ecozone`.`id`（？
数组（size = 2）
0 =>字符串'1'（长度= 1）
1 => string'2'（length = 1）
'time'=> float 1.38
解决方案
你需要使用这样的东西您的一组过滤器：
`where（function（$ q）{$ q-> where（） - > （）;}）`
使内部 wheres 包裹在（...），因为通过添加简单的或者你在/ / code>你会整个查询：
//您的第二个查询不正确的数组：
select * from`contact`其中`contact`.`deleted_at`是null和
（（select select（*）from`ecozone` where`ecozone`.`deleted_at`为null
和`contact`.`ecozone_id` =`ecozone`.`id`
//这是你的第一个（）
和`ecozone`.`ecozone` LIKE？
//这是orWhere，这使得查询错误
或`ecozone`.` ecozone` LIKE？和`ecozone`.`deleted_at`为null）
> = 1）limit 10 offset 0
您已经对顶级过滤器执行了此操作，b不在 whereHas
所以应该是这样的：
} else {
foreach（$ filters as $ column => $ filter $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ）使用（$ filter，$ model，$ column）{
$ first = array_pop（$ filter）;
$ q-> where（$ model。$ column，'LIKE' ％$ first％）;
foreach（$ filter as $ or）{
$ q-> orWhere（$ model。$ column，'LIKE'，％$ or％） ;
}
}）;
}）;
}
}
I have many tables (user facing, not SQL) with filter boxes per column. The columns have not only the tables' fields but fields of related models.
I'm trying to add a feature where one can use a comma as an OR separator when filtering data. I need to do it generally as I have a rather large amount of tables, fields and relations. Plus some tables are built dynamically.
The code that adds the where clauses for the models own fields is this:
foreach ($filters as $column => $filter) {
$q->where(function ($q) use ($filter, $model, $column) {
$first = array_pop($filter);
$q->where("$model.$column", 'LIKE', "%$first%");
foreach ($filter as $or) {
$q->orWhere("$model.$column", 'LIKE', "%$or%");
}
});
}
This works as expected. When trying to do the same thing with a relations fields the only difference is using whereHas and including the model:
foreach ($filters as $column => $filter) {
$q->whereHas($model, function ($q) use ($filter, $model, $column) {
$first = array_pop($filter);
$q->where("$model.$column", 'LIKE', "%$first%");
foreach ($filter as $or) {
$q->orWhere("$model.$column", 'LIKE', "%$or%");
}
});
}
The problem is that if I filter on a relations fields it doesn't work as expected. I suspect it has to do with whereHas with a sub where/orWhere clause. This is the output I get
No filters:
|------------+-----------------------------------+--------------------------------------|
| Contact ID | Type (Field in contact SQL table) | Ecozone (Field in Ecozone SQL table) |
|------------+-----------------------------------+--------------------------------------|
| Filter -> | | |
|------------+-----------------------------------+--------------------------------------|
| 1 | Manager | Bush |
| 2 | | Forest |
| 3 | Worker | |
|------------+-----------------------------------+--------------------------------------|
Single filter on current model (correct):
|------------+-----------------------------------+--------------------------------------|
| Contact ID | Type (Field in contact SQL table) | Ecozone (Field in Ecozone SQL table) |
|------------+-----------------------------------+--------------------------------------|
| Filter -> | man | |
|------------+-----------------------------------+--------------------------------------|
| 1 | Manager | Bush |
|------------+-----------------------------------+--------------------------------------|
Multiple filter on current model (correct):
|------------+-----------------------------------+--------------------------------------|
| Contact ID | Type (Field in contact SQL table) | Ecozone (Field in Ecozone SQL table) |
|------------+-----------------------------------+--------------------------------------|
| Filter -> | man, wor | |
|------------+-----------------------------------+--------------------------------------|
| 1 | Manager | Bush |
| 3 | Worker | |
|------------+-----------------------------------+--------------------------------------|
Single filter on related model field (correct):
|------------+-----------------------------------+--------------------------------------|
| Contact ID | Type (Field in contact SQL table) | Ecozone (Field in Ecozone SQL table) |
|------------+-----------------------------------+--------------------------------------|
| Filter -> | | bus |
|------------+-----------------------------------+--------------------------------------|
| 1 | Manager | Bush |
|------------+-----------------------------------+--------------------------------------|
Multiple filter on related model field (incorrect):
|------------+-----------------------------------+--------------------------------------|
| Contact ID | Type (Field in contact SQL table) | Ecozone (Field in Ecozone SQL table) |
|------------+-----------------------------------+--------------------------------------|
| Filter -> | | bus,for |
|------------+-----------------------------------+--------------------------------------|
| 1 | Manager | Bush |
| 2 | | Forest |
| 3 | Worker | |
|------------+-----------------------------------+--------------------------------------|
The whole code path is below:
In controller:
return Contacts::with(
'ecozone'
)->where( function ($q) {
$this->set_filters($q, 'contact');
})->paginate($count);
In BaseController:
protected function set_filters($q, $current_model) {
$filters_array = $this->parse_filters();
if ($filters_array) {
foreach($filters_array as $model => $filters) {
if ($model == $current_model) {
foreach($filters as $column => $filter) {
$q->where(function ($q) use ($filter, $model, $column) {
$first = array_pop($filter);
$q->where("$model.$column", 'LIKE', "%$first%");
foreach($filter as $or) {
$q->orWhere("$model.$column", 'LIKE', "%$or%");
}
});
}
} else {
foreach($filters as $column => $filter) {
$q->whereHas($model, function ($q) use ($filter, $model, $column) {
$first = array_pop($filter);
$q->where("$model.$column", 'LIKE', "%$first%");
foreach($filter as $or) {
$q->orWhere("$model.$column", 'LIKE', "%$or%");
}
});
}
}
}
}
}
$this->filters is:
protected function parse_filters() {
$filters = Input::get('filters');
$filters = json_decode($filters);
$filters = array_where($filters, function($key, $value) {
return !empty($value);
});
$filters = (Array) $filters;
$has_filters = !empty($filters);
if ($has_filters) {
$filters_array = [];
foreach ($filters as $key => $value) {
$value = explode(',', $value);
array_set($filters_array, $key, $value);
}
} else {
$filters_array = false;
}
return $filters_array;
}
And returns, what I think is correct, the following array. First level is model, second is field, third is comma separated OR clauses
Array (
[ecozone] => Array
(
[ecozone] => Array
(
[0] => bush
[1] => forest
)
)
[contact] => Array
(
[contact_type] => Array
(
[0] => manager
[1] => worker
)
)
)
A dump of the SQL being used when filtering on current model fields. This works as expected:
array (size=4)
0 =>
array (size=3)
'query' => string 'select count(*) as aggregate from `contact` where `contact`.`deleted_at` is null and ((`contact`.`physical_address` LIKE ? or `contact`.`physical_address` LIKE ?))' (length=163)
'bindings' =>
array (size=2)
0 => string '%add%' (length=5)
1 => string '%nana%' (length=6)
'time' => float 1.67
1 =>
array (size=3)
'query' => string 'select * from `contact` where `contact`.`deleted_at` is null and ((`contact`.`physical_address` LIKE ? or `contact`.`physical_address` LIKE ?)) limit 10 offset 0' (length=161)
'bindings' =>
array (size=2)
0 => string '%add%' (length=5)
1 => string '%nana%' (length=6)
'time' => float 0.91
2 =>
array (size=3)
'query' => string 'select * from `ecozone` where `ecozone`.`deleted_at` is null and `ecozone`.`id` in (?, ?)' (length=89)
'bindings' =>
array (size=2)
0 => string '1' (length=1)
1 => string '2' (length=1)
'time' => float 0.8
A SQL query dump of filtering by a related models fields. This doesn't work as expected:
array (size=4)
0 =>
array (size=3)
'query' => string 'select count(*) as aggregate from `contact` where `contact`.`deleted_at` is null and ((select count(*) from `ecozone` where `ecozone`.`deleted_at` is null and `contact`.`ecozone_id` = `ecozone`.`id` and `ecozone`.`ecozone` LIKE ? or `ecozone`.`ecozone` LIKE ? and `ecozone`.`deleted_at` is null) >= 1)' (length=301)
'bindings' =>
array (size=2)
0 => string '%grass%' (length=7)
1 => string '%bush%' (length=6)
'time' => float 1.18
1 =>
array (size=3)
'query' => string 'select * from `contact` where `contact`.`deleted_at` is null and ((select count(*) from `ecozone` where `ecozone`.`deleted_at` is null and `contact`.`ecozone_id` = `ecozone`.`id` and `ecozone`.`ecozone` LIKE ? or `ecozone`.`ecozone` LIKE ? and `ecozone`.`deleted_at` is null) >= 1) limit 10 offset 0' (length=299)
'bindings' =>
array (size=2)
0 => string '%grass%' (length=7)
1 => string '%bush%' (length=6)
'time' => float 0.89
2 =>
array (size=3)
'query' => string 'select * from `ecozone` where `ecozone`.`deleted_at` is null and `ecozone`.`id` in (?, ?)' (length=89)
'bindings' =>
array (size=2)
0 => string '1' (length=1)
1 => string '2' (length=1)
'time' => float 1.38
解决方案
You need to use something like this for all your sets of filters:
`where(function($q){$q->where()->orWhere();})`
to make those inner wheres wrapped in (...) because by adding simple orWhere you mess up whole query:
// your 2nd query in the incorrect array:
select * from `contact` where `contact`.`deleted_at` is null and
((select count(*) from `ecozone` where `ecozone`.`deleted_at` is null
and `contact`.`ecozone_id` = `ecozone`.`id`
// this is your first where()
and `ecozone`.`ecozone` LIKE ?
// and this is orWhere, which make the query wrong
or `ecozone`.`ecozone` LIKE ? and `ecozone`.`deleted_at` is null)
>= 1) limit 10 offset 0
You already did that for top level filters, but not in whereHas
So it should look like this:
} else {
foreach($filters as $column => $filter) {
$q->whereHas($model, function ($q) use ($filter, $model, $column) {
$q->where(function ($q) use ($filter, $model, $column) {
$first = array_pop($filter);
$q->where("$model.$column", 'LIKE', "%$first%");
foreach($filter as $or) {
$q->orWhere("$model.$column", 'LIKE', "%$or%");
}
});
});
}
}
这篇关于在模型和相关模型的领域中查询，使用或其中的多个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
