Selecting Items Based on Category

Question

I have two tables 'category' and 'items'. I would like to select five items from each category. I tried my code but it's not working. How can I do it in Laravel?

<?php

$items = DB::table('items')
    ->leftjoin('category', 'category.id', '=', 'items.categoryId')
    ->select('items.id', 'items.name', 'items.categoryId', 'category.categoryName')
    ->groupBy(items . categoryId)
    ->limit(5)
    ->get()

one trick is use UNION. also have a look at this http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group — PrinceG, Jan 28 '16 at 08:20

score 1 · Answer 1 · answered Jan 28 '16 at 08:50

you could try eager loading items, but I'm not sure how to apply a limit to that. Your best bet might be using multiple queries:

class Category extends Model{
    ...
    public function items(){
        return $this->belongsToMany(App\Item::class);
    }
}
class Item extends Model{
...
}
$categories = Category::all();

$categoriesWithItems = $categories->map(function($category){
    return [
        'category' => $category,
        'items' => $category->items()->take(5)->get(),
    ];
}

score 0 · Answer 2 · answered Jan 28 '16 at 09:00

0

If you prefere the flaxability of hand-crafted queries, you could do:

// Build and execute query
$sql = "
  SELECT *
  FROM products prod
  JOIN products_categories prodcat ON prodcat.product_id = prod.product_id
  WHERE (product_parent_id = 0 AND product_status = 'published')
  GROUP BY prod.category_id
";
Log::info('getProducts(): ', ['category_id' => $category_id, 'sql' => $sql, 'where' => $where]);
$result = DB::select($sql);

$result will contain an array of objects

answered Jan 28 '16 at 09:00

Brian

8,418
2
25
32

Well it is using the query in my own project, was providing as an example. – Brian Jan 28 '16 at 09:06
That i know. What is that Log::info means. I'm getting error on that part – radiis Jan 28 '16 at 09:11

score 0 · Answer 3 · answered Jan 28 '16 at 09:15

Laravel 5 In Category Model add this relationship

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

In Item Model add this relationship

public function category()
  {
    return $this->belongsTo(Category::class);
  }

Then in controller you can simply write like this

$categories = Category::all();
        return view('tasks.item', [
      'categories' => $categories
    ]);

In view make a foreach loop through category and then call another foreach loop though your items upto 5

@foreach ($categories as $category)
                            <tr>
                                <!-- Task Name -->
                                <td class="table-text">
                                    <div>{{ $category->category_name }}</div>
                                </td>
                                <td>
                                    @foreach($category->items->take(5) as $item)
                                        {{ $item->name }}
                                    @endforeach
                                </td>
                            </tr>
                        @endforeach

Selecting Items Based on Category

3 Answers3