stuck at DB query, laravel

Question

I need your help... Can't figure out where the problem is. I am trying to show all products of a subcategory.Sometimes it shows the first or the last record. Then it repeats many times the same record( as the cycle is).

category: id, name, visible

products:id, name, 

category_products:id, id_product, id_category

Route::get('navigation/{id}',function($id){
  $prods= \App\Products_to_Categories::where('id_category',$id)->get();
     $products=array();

    foreach ($prods as $prod)
    {
       $products[] = \App\Products::find($prod->id_product)->
                      where('visible','yes')
                     -> where('delete','no')->first();

    }


    return view('subcategories.order_products',
        ['products'=>$products ]);}

View blade

<div class="col-md-6 col-md-offset-1">
                            <ul id="sortable">
                                @foreach($products as $product)
                                    <li class="ui-state-default" id="{{ $product->id}}"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span> {{$product->name}}</li>
                                @endforeach
                            </ul>

                        </div>

Jose Rodriguez · Answer 1 · 2018-10-29T23:51:18.113

0

Try this in Laravel >= 5.2: :

 $prods= \App\Products_to_Categories::where('id_category',$id)->get();
 $products=array();
 $whereIn = array();

 foreach ($prods as $prod)
 {
     $whereIn[] = $prod->id_product;
 }

 $products[] = \App\Products::find($prod->id_product)
                ->where('visible','yes')
                -> where('delete','no')
                ->whereIn('id', $whereIn)
                ->orderByRaw('RAND()')
                ->get();

This will give you the list of products of a specific category in random order.

Laravel 4.2.7 - 5.1:

User::orderByRaw("RAND()")->get();

Laravel 4.0 - 4.2.6:

User::orderBy(DB::raw('RAND()'))->get();

Laravel 3:

User::order_by(DB::raw('RAND()'))->get();

source : Laravel - Eloquent or Fluent random row

edited Oct 29 '18 at 23:51

answered Oct 29 '18 at 23:07

Jose Rodriguez

251
2
7

Thank you ... but it shows property [id] does not exist on this collection instance – alphal Oct 29 '18 at 23:12
you have to make sure that you have the "id" property on your product model. – Jose Rodriguez Oct 29 '18 at 23:14
Instead of get(), i used first().... $products[] = \App\Products::find($prod->id_product) ->where('visible','yes') -> where('delete','no') ->whereIn('id', $whereIn) ->inRandomOrder() ->first(); but it shows me only one record (which isn't right, because it has to show me more than 1) – alphal Oct 29 '18 at 23:22
I just updated the example, try this : orderByRaw('RAND()') – Jose Rodriguez Oct 29 '18 at 23:51

score 0 · Accepted Answer · answered Oct 30 '18 at 00:48

It looks like products and categories are related through the join table category_products, so you can setup a belongsToMany() relationship and query from Category to Product without looping over the join table.

https://laravel.com/docs/5.7/eloquent-relationships#many-to-many

Category model:

public function products()
{
    return $this->belongsToMany(\App\Products::class, 'category_products', 'id_category', 'id_product');
}

Products model:

public function categories()
{
    return $this->belongsToMany(\App\Category::class, 'category_products', 'id_product', 'id_category');
}

Controller code:

$category = Category::find($id);
$products = $category->products()
                     ->where('visible', 'yes')
                     ->where('delete', 'no')
                     // ->inRandomOrder()   // un-comment this if you want results in random order
                     ->get();

stuck at DB query, laravel

2 Answers2