CODEWITHSUNDEEP
phplaraveleloquent
s_h
s_h

Reputation: 1496

Laravel How to create One Query in order to Produce Multiple Foreach on View

For performance, I'm trying to create only one query to db and display multiple foreach in view blade. In order to produce this result, my approach was to use query builder and selectRaw to create filters.

My current code:

$totals = DB::table('product')->select('productName','sellingPrice','productSpecification')
            ->selectRaw("(case where categoryId = '177' then 1 end) as computers")
            ->selectRaw("(case where categoryId = '31388' then 1 end) as cameras")
            ->selectRaw("(case where categoryId = '9355' then 1 end) as mobiles")
            ->get();
    }

The idea is to use selectraw in the following way at blade views:

@foreach ($totals->computers as $productcomputers)

@foreach ($totals->cameras as $productcameras)

@foreach ($totals->mobiles as $productmobiles)

I tried the approach but Im receiving error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where categoryId = '177' then 1 end) as computers

Any suggestion to produce one query with multiple foreach in laravel or to solve the current approach?

brgds,

Upvotes: 0

Views: 300

Answers (2)

Mukti Rani Ghosh
Mukti Rani Ghosh

Reputation: 577

Try this

$totals = DB::table('product')->select('productName','sellingPrice','productSpecification')
            ->whereIn('categoryId', [177, 31388, 9355])
            ->selectRaw("(case when categoryId = '177' then 1 end) as computers")
            ->selectRaw("(case when categoryId = '31388' then 1 end) as cameras")
            ->selectRaw("(case when categoryId = '9355' then 1 end) as mobiles")
            ->get();

        $data = collect();
        $data->computers = $totals->where('computers', '!=', null);
        $data->cameras   = $totals->where('cameras', '!=', null);
        $data->mobiles   = $totals->where('mobiles', '!=', null);

Now use at blade views

@foreach ($data->computers as $productcomputers)

@foreach ($data->cameras as $productcameras)

@foreach ($data->mobiles as $productmobiles)

Hope this will help you.

Upvotes: 3

Bram Verstraten
Bram Verstraten

Reputation: 1557

Replace case where by case when. You can check the documentation at this link.

Upvotes: 1

Related Questions