How can I write if in query in laravel without breaking the chain and sequence

Question

I have the following query i need if statement in the query how can i do that. the following query gets the list of products based on manufacturers. If tried if after this query and then use get(); but that not work for me

Query:

  $productsFeatured = Product::select('products.low_emitting_material_prerequisite_file',
        'manufacturers.phone','manufacturers.address','manufacturers.linkedin_url','manufacturers.city', 
        'products.specs_file','products.specs_file_url','products.low_emitting_material_credit_file', 
        'products.id', 'products.name', 'products.logo', 'products.manufacturer_id',
        'products.division_id', 'products.section_id', 'products.website', 'divisions.name as division_name', 'divisions.code as division_code',
        'sections.name as section_name', 'sections.code'   
         )
                ->whereIn('products.status', ['active'])
                ->where(function ($query) {
                    $query->where('products.low_emitting_material_prerequisite_file', '!=', '');
                })
                ->where('users.user_type', 'manufacturer_paid')
                ->leftJoin('products_selected_leeds', 'products.id', '=', 'products_selected_leeds.product_id')
                ->leftJoin('sections', 'products.section_id', '=', 'sections.id')
                ->leftJoin('divisions', 'products.division_id', '=', 'divisions.id')
                ->join('manufacturers', 'products.manufacturer_id', '=', 'manufacturers.id')
                ->join('users', 'manufacturers.user_id', '=', 'users.id')
                ->orderBy('divisions.code', 'asc')
                ->orderBy('sections.code', 'asc')
                ->groupBy('products.id');
                ->get()

What I want

        $productsFeatured = Product::select('products.low_emitting_material_prerequisite_file',
        'manufacturers.phone','manufacturers.address','manufacturers.linkedin_url','manufacturers.city', 
        'products.specs_file','products.specs_file_url','products.low_emitting_material_credit_file', 
        'products.id', 'products.name', 'products.logo', 'products.manufacturer_id',
        'products.division_id', 'products.section_id', 'products.website', 'divisions.name as division_name', 'divisions.code as division_code',
        'sections.name as section_name', 'sections.code'   
         )
         $productsFeatured ->whereIn('products.status', ['active'])
                if($request->chps_approved ==  '63'){
                    $productsFeatured->where(function ($query) {
                        $query->where('products.low_emitting_material_prerequisite_file', '!=', '');
                    })
                }

                ->where('users.user_type', 'manufacturer_paid')
                ->leftJoin('products_selected_leeds', 'products.id', '=', 'products_selected_leeds.product_id')
                ->leftJoin('sections', 'products.section_id', '=', 'sections.id')
                ->leftJoin('divisions', 'products.division_id', '=', 'divisions.id')
                ->join('manufacturers', 'products.manufacturer_id', '=', 'manufacturers.id')
                ->join('users', 'manufacturers.user_id', '=', 'users.id')
                ->orderBy('divisions.code', 'asc')
                ->orderBy('sections.code', 'asc')
                ->groupBy('products.id');
                 ->get()

score 0 · Answer 1 · answered Jun 18 '19 at 16:02

You can just:

$productsFeatured ->whereIn('products.status', ['active']);

if($request->chps_approved ==  '63'){
    $productsFeatured->where(function ($query) {
        $query->where('products.low_emitting_material_prerequisite_file', '!=', '');
    })
}

$productsFeatured->where('users.user_type', 'manufacturer_paid')
    ->leftJoin('products_selected_leeds', 'products.id', '=', 'products_selected_leeds.product_id')
    ->....
    ->get();

Chainable methods are simply returning the original class instance (in this case $productsFeatured) which is then used to call the next method.

i.e

$class->methodOne()->methodTwo();

is identical to

$class->methodOne();
$class->methodTwo();

providing the methods have been made chainable: PHP method chaining?

error Argument 1 passed to Illuminate\Database\Grammar::parameterize() must be of the type array, string given, called in — Abdullah Faraz, Jun 18 '19 at 16:17
Would need to see a full stack trace to be sure, but the error message suggests that you are passing in an array to one of your where statements when it's expecting a string — Nick Thompson, Jun 19 '19 at 16:08

score 0 · Answer 2 · answered Jun 18 '19 at 23:49

0

You should use conditional clauses.

With this method you could write:

// ...
$productsFeatured ->whereIn('products.status', ['active'])
    ->when($request->chps_approved ==  '63', function ($query) {
        return $query->where('products.low_emitting_material_prerequisite_file', '!=', '');
    })
    ->where('users.user_type', 'manufacturer_paid')
 // continue to chain methods for the query...

answered Jun 18 '19 at 23:49

mdexp

3,492
2
9
20

`Call to undefined method Illuminate\Database\Query\Builder::when()` – Abdullah Faraz Jun 19 '19 at 12:15
Which version of Laravel are you using? – mdexp Jun 19 '19 at 12:54

How can I write if in query in laravel without breaking the chain and sequence

2 Answers2