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

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]);

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

$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.