Query doubled results when load sub-categories in Laravel

Question

I have strange problem and can't understand from where its came. On my page I have Top Level category. When I click on top level category is open page with all sub-categories which they have products.

The problem is that if I have 2 products in sub-category_1 I see two times sub-category_1 on the page.

This is the controller which I have

public function showSubCats($categoryId) {

$subcats = SubCategories::select('*', DB::raw('sub_category.sub_cat_id AS sub_cat_id'))
    ->leftJoin('products', function($join) {
             $join->on('products.sub_cat_id', '=', 'sub_category.sub_cat_id'); 
           })
        ->where('sub_category.category_id', '=', $categoryId)
        ->whereNotNull('products.sub_cat_id')
        ->get();

     return View::make('site.subcategory', [            
          'subcats' => $subcats             
     ]); 
}

Here is the view

 @foreach($subcats as $i => $subcategory)   

               // html
 @endforeach

This is the result.. should be one sub-category with two products inside.. now I have two identical sub-cats.. same products, same id..

are you sure that you have `sub_cat_id` in `sub_category` and not `id` instead? — Niklesh Raut, Jul 27 '16 at 11:40

score 1 · Accepted Answer · answered Jul 27 '16 at 11:42

1

Change your query as

$subcats = DB::table('sub_category as sc')
    ->leftJoin('products as p', 'p.sub_cat_id', '=', 'sc.sub_cat_id')//cross check this sc.sub_cat_id may be it si sc.id
    ->where('sc.category_id', '=', $categoryId)
    ->whereNotNull('p.sub_cat_id')
    ->select('*', DB::raw('sc.sub_cat_id AS sub_cat_id'))
    ->get();

answered Jul 27 '16 at 11:42

Niklesh Raut

34,013
16
75
109

Yes, it is `sub_cat_id` and with this query still got twice same sub-category as above on the image. – S.I. Jul 27 '16 at 11:54
The idea is to check if `sub-category` has products in it. So now it is check table product for `sub_cat_id` and see that there are two products with where `sub_cat_id=1` may be that's why is showing twice. Or I'm wrong? – S.I. Jul 27 '16 at 11:56
Can I just use `->groupBy('sc.sub_cat_id')`? – S.I. Jul 27 '16 at 12:02
Seems like it's working this way also.. wonder if there can be some problem later but can't think for possible scenario.. – S.I. Jul 27 '16 at 12:08
before this did you also checked by changing this `->select('*', DB::raw('sc.sub_cat_id AS sub_cat_id'))` to `->select('p.*','sc.*', DB::raw('sc.sub_cat_id AS sub_cat_id'))` ? – Niklesh Raut Jul 27 '16 at 12:12
Like this is working great. What is the difference exactly? `->select('*', DB::raw('sc.sub_cat_id AS sub_cat_id'))` to `->select('p.*','sc.*', DB::raw('sc.sub_cat_id AS sub_cat_id'))` – S.I. Jul 27 '16 at 12:19
1

You could get complete concept from here : http://stackoverflow.com/a/4779495/2815635 – Niklesh Raut Jul 27 '16 at 12:23

Query doubled results when load sub-categories in Laravel

1 Answers1