如何在许多字段上使用 1 个过滤器使 Laravel 雄辩的请求

在我的Laravel 5.7/mysql应用程序中，我在具有10个过滤器输入的表单上发出请求，如果非空，则其中一个 ($filter_search( 必须与所有字段(字符串、数字、日期，引用其他表的字段(在结果列表中。

我在此表字段上限定了范围：

public function scopeGetBySearch($query, $search = null)
{
if (empty($search)) {
return $query;
}
$tb= with(new StorageSpace)->getTable();
return $query->where(
$tb.'.number',  $search
) ->orWhere(function ($query) use ($search, $tb) {
$query->where( $tb.".notes",  'like',  '%'.$search.'%' )
->orWhere($tb.".selling_range", $search)
->orWhere($tb.".actual_storage_rent", $search)
->orWhere($tb.".insurance_vat", $search)
//                            ->havingRaw("job_ref_no", $search)
})

但是我有一个问题，如何在其他表中job_ref_no字段上设置过滤器：

$storageSpacesCollection = StorageSpace
::getByStatus($filter_status)
->getById($relatedStorageSpacesArray)
->getByLocationId($filter_location_id, 'warehouses')
->getByCapacityCategoryId($filter_capacity_category_id, 'storage_capacities')
->getByLevel($filter_level)
->getByNumber($filter_number, true)
->orderBy('storage_spaces.id', 'asc')
->getByStorageCapacityId($filter_storage_capacity_id)
->getByClientId($filter_client_id)
->getByColorId($filter_color_id)
->getBySearch($filter_search)

//->havingRaw("job_ref_no = " . $filter_search(

->leftJoin( 'storage_capacities', 'storage_capacities.id', '=', 'storage_spaces.storage_capacity_id' )
->leftJoin( 'warehouses', 'warehouses.id', '=', 'storage_spaces.warehouse_id' )
->leftJoin( 'clients', 'clients.id', '=', 'storage_spaces.client_id')
->select(
"storage_spaces.*",
DB::raw( "CONCAT(storage_capacities.count, ' ', storage_capacities.sqft ) as storage_capacity_name" ),
DB::raw("( SELECT check_ins.job_ref_no FROM check_ins WHERE // I got job_ref_no field in subquesry  check_ins.storage_space_id=storage_spaces.id ORDER BY check_ins.id ASC limit 1 ) AS job_ref_no"),
"warehouses.name as warehouse_name",
"clients.full_name as client_full_name")
->get();

havingRaw 在范围和上面的请求中都不起作用，如果要取消注释它。

我尝试使用 addSelect，例如：

但有要求：

$storageSpacesCollection = StorageSpace
::getByStatus($filter_status)
->whereRaw('storage_spaces.id <= 8') // DEBUGGING
->getById($relatedStorageSpacesArray)
->getByLocationId($filter_location_id, 'warehouses')
->getByCapacityCategoryId($filter_capacity_category_id, 'storage_capacities')
->getByLevel($filter_level)
->getByNumber($filter_number, true)
->orderBy('storage_spaces.id', 'asc')
->getByStorageCapacityId($filter_storage_capacity_id)
->getByClientId($filter_client_id)
->getByColorId($filter_color_id)
->getBySearch($filter_search)
//            ->havingRaw("job_ref_no = " . $filter_search)
->leftJoin( 'storage_capacities', 'storage_capacities.id', '=', 'storage_spaces.storage_capacity_id' )
->leftJoin( 'warehouses', 'warehouses.id', '=', 'storage_spaces.warehouse_id' )
->leftJoin( 'clients', 'clients.id', '=', 'storage_spaces.client_id')
->select(
"storage_spaces.*",
DB::raw( "CONCAT(storage_capacities.count, ' ', storage_capacities.sqft ) as storage_capacity_name" ),
"warehouses.name as warehouse_name",
"clients.full_name as client_full_name")
->addSelect([
'job_ref_no' => CheckIn::selectRaw('job_ref_no')->whereColumn('check_ins.storage_space_id', 'storage_spaces.id'),
])
->get();

但是我得到了一个错误：

local.ERROR: stripos() expects parameter 1 to be string, object given {"userId":11,"email":"nilovsergey@yahoo.com","exception":"[object] (ErrorException(code: 0): stripos() expects parameter 1 to be string, object given at /mnt/_work_sdb8/wwwroot/lar/the-box-booking/vendor/laravel/framework/src/Illuminate/Database/Query/Grammars/Grammar.php:1031)
[stacktrace]
#0 [internal function]: Illuminate\Foundation\Bootstrap\HandleExceptions->handleError(2, 'stripos() expec...', '/mnt/_work_sdb8...', 1031, Array)
#1 /mnt/_work_sdb8/wwwroot/lar/the-box-booking/vendor/laravel/framework/src/Illuminate/Database/Query/Grammars/Grammar.php(1031): stripos(Object(Illuminate\Database\Eloquent\Builder), ' as ')
#2 [internal function]: Illuminate\Database\Query\Grammars\Grammar->wrap(Object(Illuminate\Database\Eloquent\Builder))

哪种方式有效？

修改后的块：

我尝试手动创建我需要的sql。

与拉拉维尔要求：

$storageSpacesCollection = StorageSpace
::getByStatus($filter_status)
->whereRaw('storage_spaces.id <= 8') // DEBUGGING
->getById($relatedStorageSpacesArray)
->getByLocationId($filter_location_id, 'warehouses')
->getByCapacityCategoryId($filter_capacity_category_id, 'storage_capacities')
->getByLevel($filter_level)
->getByNumber($filter_number, true)
->orderBy('storage_spaces.id', 'asc')
->getByStorageCapacityId($filter_storage_capacity_id)
->getByClientId($filter_client_id)
->getByColorId($filter_color_id)
->getBySearch($filter_search)
->leftJoin( 'storage_capacities', 'storage_capacities.id', '=', 'storage_spaces.storage_capacity_id' )
->leftJoin( 'warehouses', 'warehouses.id', '=', 'storage_spaces.warehouse_id' )
->leftJoin( 'clients', 'clients.id', '=', 'storage_spaces.client_id')
->select(
"storage_spaces.*",
DB::raw( "CONCAT(storage_capacities.count, ' ', storage_capacities.sqft ) as storage_capacity_name" ),
DB::raw("( SELECT check_ins.job_ref_no FROM check_ins WHERE check_ins.storage_space_id=storage_spaces.id ORDER BY check_ins.id ASC limit 1 ) AS job_ref_no"),
"warehouses.name as warehouse_name",
"clients.full_name as client_full_name")
->get();

并跟踪我看到带有job_ref_no列的sql语句：

SELECT `storage_spaces`.*, CONCAT(storage_capacities.count, ' ', storage_capacities.sqft )     AS storage_capacity_name, (   SELECT check_ins.job_ref_no 
FROM check_ins 
WHERE check_ins.storage_space_id=storage_spaces.id 
ORDER BY check_ins.id ASC limit 1 )     AS job_ref_no, `warehouses`.`name`     AS `warehouse_name`, `clients`.`full_name`     AS `client_full_name` 
FROM `storage_spaces` 
LEFT JOIN `storage_capacities` on `storage_capacities`.`id` = `storage_spaces`.`storage_capacity_id` 
LEFT JOIN `warehouses` on `warehouses`.`id` = `storage_spaces`.`warehouse_id` 
LEFT JOIN `clients` on `clients`.`id` = `storage_spaces`.`client_id` 
WHERE storage_spaces.id <= 8     AND (`storage_spaces`.`number` = '999'     OR 

(`storage_spaces`.`notes` like '%999%'     OR 
`storage_spaces`.`selling_range` = '999'     OR 
`storage_spaces`.`actual_storage_rent` = '999'     OR 
`storage_spaces`.`insurance_vat` = '999') ) 
ORDER BY `storage_spaces`.`id` asc

我想手动设置job_ref_no列上的过滤器：

SELECT `storage_spaces`.*, CONCAT(storage_capacities.count, ' ', storage_capacities.sqft )     AS storage_capacity_name, (   SELECT check_ins.job_ref_no 
FROM check_ins 
WHERE check_ins.storage_space_id=storage_spaces.id 
ORDER BY check_ins.id ASC limit 1 )     AS job_ref_no, `warehouses`.`name`     AS `warehouse_name`, `clients`.`full_name`     AS `client_full_name` 
FROM `storage_spaces` 
LEFT JOIN `storage_capacities` on `storage_capacities`.`id` = `storage_spaces`.`storage_capacity_id` 
LEFT JOIN `warehouses` on `warehouses`.`id` = `storage_spaces`.`warehouse_id` 
LEFT JOIN `clients` on `clients`.`id` = `storage_spaces`.`client_id` 
WHERE storage_spaces.id <= 8     AND (`storage_spaces`.`number` = '999'     OR 

(`storage_spaces`.`notes` like '%999%'     OR 
`storage_spaces`.`selling_range` = '999'     OR 
`storage_spaces`.`actual_storage_rent` = '999'     OR 
`storage_spaces`.`insurance_vat` = '999' OR 
job_ref_no = '999' )   // I added this line @
) 
ORDER BY `storage_spaces`.`id` asc

但是我得到了错误：

Error in query (1054): Unknown column 'job_ref_no' in 'where clause'

在原始sql中，哪种有效方式以及如何雄辩地实现它？

修改后的块#2：我尝试用join制作：

$storageSpacesCollection = StorageSpace
::getByStatus($filter_status)
->getById($relatedStorageSpacesArray)
->getByLocationId($filter_location_id, 'warehouses')
->getByCapacityCategoryId($filter_capacity_category_id, 'storage_capacities')
->getByLevel($filter_level)
->getByNumber($filter_number, true)
->orderBy('storage_spaces.id', 'asc')
->getByStorageCapacityId($filter_storage_capacity_id)
->getByClientId($filter_client_id)
->getByColorId($filter_color_id)
->getBySearch($filter_search)
->leftJoin( 'storage_capacities', 'storage_capacities.id', '=', 'storage_spaces.storage_capacity_id' )
->leftJoin( 'warehouses', 'warehouses.id', '=', 'storage_spaces.warehouse_id' )
->leftJoin( 'clients', 'clients.id', '=', 'storage_spaces.client_id')
->leftJoin( 'check_ins', 'check_ins.storage_space_id', '=', 'storage_spaces.id') // I ADDED THIS LINE
->select(
"storage_spaces.*",
DB::raw( "CONCAT(storage_capacities.count, ' ', storage_capacities.sqft ) as storage_capacity_name" ),
//                DB::raw("( SELECT check_ins.job_ref_no FROM check_ins WHERE check_ins.storage_space_id=storage_spaces.id ORDER BY check_ins.id ASC limit 1 ) AS job_ref_no"),
"warehouses.name as warehouse_name",
"check_ins.job_ref_no as job_ref_no",
"clients.full_name as client_full_name")
->distinct()
->get();

我有一个sql：

SELECT distinct `storage_spaces`.*, CONCAT(storage_capacities.count, ' ', storage_capacities.sqft )     AS storage_capacity_name, 
`warehouses`.`name`     AS `warehouse_name`, `check_ins`.`job_ref_no`     AS `job_ref_no`, `clients`.`full_name`     AS `client_full_name` 
FROM `storage_spaces` 
LEFT JOIN `storage_capacities` on `storage_capacities`.`id` = `storage_spaces`.`storage_capacity_id` 
LEFT JOIN `warehouses` on `warehouses`.`id` = `storage_spaces`.`warehouse_id` 
LEFT JOIN `clients` on `clients`.`id` = `storage_spaces`.`client_id` 
LEFT JOIN `check_ins` on `check_ins`.`storage_space_id` = `storage_spaces`.`id` 
WHERE ( `storage_spaces`.`number` = 'S1-102'     OR (`storage_spaces`.`notes` like '%S1-102%'     OR `storage_spaces`.`selling_range` = 'S1-102'     OR 
`storage_spaces`.`actual_storage_rent` = 'S1-102'     OR `storage_spaces`.`insurance_vat` = 'S1-102'     OR `job_ref_no` = 'S1-102') ) 
ORDER BY `storage_spaces`.`id` asc

我有不同的结果

我只需要从check_ins获取最后一行，这就是为什么在我的请求中我有限制 1：

DB::raw("( SELECT check_ins.job_ref_no FROM check_ins WHERE check_ins.storage_space_id=storage_spaces.id ORDER BY check_ins.id ASC limit 1 ) AS job_ref_no"),

这就是为什么有几行与所有 AE 一样check_ins storage_spaces 2(我有storage_spaces的所有行，我不明白为什么

我试图用加入/右加入更改左加入，但它没有帮助......

谢谢！

您需要将联接设置为主查询而不是选择查询，因为在选择查询中，它不是您设置为 where 条件的实际列。

我在范围内找到了一个决策插件：

->orWhereRaw("(SELECT check_ins.job_ref_no FROM check_ins WHERE
check_ins.storage_space_id=storage_spaces.id ORDER BY check_ins.id ASC LIMIT 1) = '".$search."'")

这对我有用。

相关内容

最新更新

热门标签：