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

121

mstdmstd：

在我的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在范围和上述请求中均不起作用。

2）我尝试使用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":"[email protected]","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。与Laravel请求：

$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：我尝试加入：

        $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

我有不同的结果1）我只需要从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"),

这就是为什么有几行作为storage_spaces的行，因为所有check_ins ae已加入2）我有来自storage_spaces的所有行，但我看不到为什么

3）我试图用Join / rightJoin更改leftJoin，但这没有帮助...

谢谢！

mstdmstd：

我在作用域中发现了一个决定插件：

->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."'")

那对我有用。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。