I want items to be under the category they are related to

Question

I want to achieve this result, but I can't understand the logic of the sql implementation

here is my welcome.blade.php:

@extends('layouts/layout')
@section('content')

<div class="container-fluid">
    @foreach ($categories as $category)
    <h4>{{$category->category_name}}</h4>
        <div class="row">
            <div class="col-sm-4">
                <h6>{{$category->item_name}}</h6>
            </div>
        </div>
        
    @endforeach
</div>
@endsection

and my controller:

    $categories = DB::table('items')
        ->join('categories', 'items.category_id', 'categories.id')
        ->select('items.item_name', 'items.item_price', 'categories.category_name', 'categories.description')
        ->groupBy('categories.category_name')
        ->get();

return view('welcome', 'categories'=> $categories]);

Peppermintology · Accepted Answer · 2021-10-24T19:15:28.223

2

You can use the QueryBuilder here if you want, but I would suggest using Eloquent unless you have a particular reason to not use it.

Let's assume that you have a Category and Item model and that you've defined a relational function within the Category model back to the Item model.

class Category extends Model
{
    public function items()
    {
        // this assumes you're using standard naming conventions
        return $this->hasMany(Item::class);
    }
}

Then in your Controller, you might do something like the following to obtain categories and their related items.

$categories = Category::with('items')->get();

return view('welcome', compact('categories'));

The above can be adapted when required to limit the query to a specific Category when you require it. We use the with method to eager load the related records so that we can avoid the n+1 query problem.

Finally, in your view you can iterate (loop) over the categories and loop over the related items (there could be more than one).

@forelse($categories as $category)
    <h4>{{ $category->category_name }}</h4>
    @forelse ($category->items as $item)
        <p>{{ $item->item_name }}</p>
    @empty
        <p>No items</p>
    @endforelse
@empty
    <p>No results</p>
@endforelse

The above is purely illustrative and ideally, you would break the above into reusable components.

edited Oct 24 '21 at 19:15

answered Oct 24 '21 at 18:59

Peppermintology

9,343
3
27
51

@AmirAdamov Yeah, I misspelt `items` in the `with('items')` method. Updated my answer to correct the typo. – Peppermintology Oct 24 '21 at 19:46
thank you it was solved. Now new error arrived:D Invalid argument supplied for foreach() – Amir Adamov Oct 24 '21 at 19:55
@AmirAdamov Can you provide the full error? – Peppermintology Oct 24 '21 at 20:00
it is 3rd line of ur view code...@forelse ($category->items as $item) – Amir Adamov Oct 24 '21 at 20:09
oh it works now, the problem was items that you misspelled, my model name was item. Thank you!!!! – Amir Adamov Oct 24 '21 at 20:25
@AmirAdamov Pleased it is sorted. [: – Peppermintology Oct 24 '21 at 20:31

score 1 · Answer 2 · edited Oct 24 '21 at 19:10

Since you're using Laravel, you could use Eloquent relationships:

class Category extends Model{

   public function items{
     return $this->hasMany(Item::class);
   }
}

Then in your controller:

$categories = Category::with('items')->get();
return view('welcome', ['categories'=> $categories]);

Then in your view:

@foreach ($categories as $category)
<h4>{{$category->category_name}}</h4>
    @foreach($category->items as item)
    <div class="row">
        <div class="col-sm-4">
            <h6>{{$item->name}}</h6>
        </div>
    </div>
    @endforeach
    
@endforeach

score 0 · Answer 3 · answered Oct 24 '21 at 19:04

$categories = DB::table("categories")->select("id", "category_name", "description")->get();

$items = DB::table("items")->select("category_id", "item_name", "price")->get();

view('welcome',compact("categories", "items"));


/////////////////////// BLADE ///////////////////////
<div class="container-fluid">
    @foreach ($categories as $category)
      @foreach($items as $item)
      @if($category->id === $item->category_id)
    <h4>{{$category->category_name}}</h4>
        <div class="row">
            <div class="col-sm-4">
                <h6>{{$category->item_name}}</h6>
            </div>
        </div>
        @endif
        @endforeach
    @endforeach
</div>

Try this.

I want items to be under the category they are related to

3 Answers3