我有许多表(面向用户，而不是SQL)，每列都有过滤框。列中不仅包含表的字段，还包含相关模型的字段。
我试图添加一个功能，在过滤数据时可以使用逗号作为OR分隔符。我通常需要这样做，因为我有相当大的数量的表，字段和关系。另外，有些表是动态构建的。
为模型自己的字段添加where子句的代码如下: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%");
}
});
}
按预期工作。当试图对关系字段做同样的事情时，唯一的区别是使用whereHas和包括模型:
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%");
}
});
}
问题是，如果我对关系字段进行过滤，它不会像预期的那样工作。我怀疑它与where子where/orWhere子句有关。这是我得到的输出
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 | |
|------------+-----------------------------------+--------------------------------------|
整个代码路径如下:
在控制器:return Contacts::with(
'ecozone'
)->where( function ($q) {
$this->set_filters($q, 'contact');
})->paginate($count);
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 ->过滤器是:
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;
}
返回，我认为是正确的，下面的数组。第一级是模型，第二级是字段，第三级是逗号分隔的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' => 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
根据相关模型字段进行筛选的SQL查询转储。这没有像预期的那样工作:
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
您需要对所有过滤器集使用如下内容:
`where(function($q){$q->where()->orWhere();})`
使那些内部的wheres包裹在(...)中，因为通过添加简单的orWhere你会弄乱整个查询:
// 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
您已经为顶级过滤器这样做了，但没有在whereHas
应该是这样的
} 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%");
}
});
});
}
}