How to implement such request to DB

Question

In view I have two foreach: first outputs all categories, the second one, should output every brand from current category.

Example:

// Controller
$categories = DB::table('products')
                ->join('categories', 'products.c_id', '=', 'categories.id')
                ->select('categories.name as cat')
                ->groupBy('categories.name')
                ->get();
$brands = DB::table('products')
                ->join('categories', 'products.c_id', '=', 'categories.id')
                ->join('brands', 'products.b_id', '=', 'brands.id')
                ->select('brands.name as brand')
                ->where('categories.id', '=', '1')<-- the problem part
                ->groupBy('brands.name')
                ->get();

//view
@foreach($categories as $category)
    <li><a>{{ $category->cat}}</a>
        <ul class="sort-section">
            <li><a href="#"><strong>All {{$category->cat}}</strong></a></li>
            @foreach($brands as $brand)
                <li><a href="#">{{ $brand->brand }}</a></li>
            @endforeach
        </ul>
    </li>
@endforeach

I don't know how to output it properly. Right now I'm getting records, where categories.id = 1(I did it to check if my code works at all), but instead of 1 I should use id of current category. In other words, if I would like to make it happen no matter what, I would implement the second query in first foreach in my view(which is crazy).

Any suggestions?

Answer 1

You can do with just one query, like this:

// Controller

$getAll = DB::table('categories')
                    ->leftJoin('products', 'products.c_id', '=', 'categories.id')
                    ->leftJoin('brands', 'brands.id', '=', 'products.b_id')
                    ->groupBy('brands.name')
                    ->groupBy('categories.name')
                    ->get([
                        'categories.id as id',
                        'categories.name as cat',
                        'brands.name as brand'
                    ]);

        $categories = [];
        if (count($getAll)) {
            foreach ($getAll as $single) {
                if (!isset($categories[$single->id])) {
                    $categories[$single->id] = new \StdClass();
                }
                $categories[$single->id]->cat = $single->cat;
                $categories[$single->id]->brands[] = $single->brand;
            }
        }

Then in your view do this

@foreach($categories as $category)
 <li><a>{{ $category->cat}}</a>
   <ul class="sort-section">
     <li><a href="#"><strong>All {{$category->cat}}</strong></a></li>
     @foreach($category->brands as $brand)
        <li><a href="#">{{ $brand }}</a></li>
     @endforeach
   </ul>
 </li>
@endforeach

Answer 2

There are two different ways you can approach this.

The first is the uglier way, but still does the trick. You can split this into two different parts. The user first selects the Category, which submits a post request, and then you capture the Category ID and then query and return only the brands associated with that category. This does force a page reload.

The second is the cleaner way, and it's using AJAX. Using JQuery makes this easy, and here's an answer that can show you how to get started: Using AJAX