How to filter a belongsToMany relationship? Laravel 5.4

Question

I've created an application to filter data. Inside of index, I made a filter to filter products by description, model, status, stock and category. My category is organized through the erp_categoy table and the relationship with the product is made through erp_product_category (Receiving Product ID + Category ID). I created a BelongsToMany relationship in the Model: Product, Category and ProductCategory.

Product Model

public function category()
    {
        return $this->belongsToMany('App\Category', 'erp_product_category', 'erp_productid', 'erp_categoryid');
    }

Category Model

public function product()
    {
        return $this->belongsToMany('App\Product','erp_product_category', 'erp_categoryid', 'erp_productid');
    }

Product_category Model

public function product()
    {
        return $this->belongsTo('App\Models\Product', 'erp_categoryid', 'erp_categoryid');
    }

In my index I created a select one that lists all categories of the 'erp_category' table.

<select id="categoria" name="categoria" class="form-control" style="width: 150px;">
                                @foreach($cat as $categoria)
                                    <option value="{{$categoria->erp_categoryid}}" @if($categoria->erp_categoryid === session('categoria')) selected  @endif >{{$categoria->erp_name}}</option>
                                @endforeach
                            </select>

I've extended my JavaScript and stored the values in a session 'category'.

<script src="{{ asset('assets/js/ProductSearch.js') }}"></script>
    <script>
        var postSearch = '{{ route('product::searchPost') }}';
        var searchRequest = {
            'categoria' :'{{session('categoria')}}',
        };

    </script>

And then I did the research through JS.

$(document).on('blur', '#categoria', function(){
        var categoria = $('#categoria').val();
        searchRequest['categoria'] = categoria;
        doSearch();
    });

function doSearch() {
    $.post(postSearch, {
            'search_data': JSON.stringify(searchRequest),
            '_token': $('meta[name=csrf-token]').attr('content'),
        }
        , function(data) {
            $('#product-table').html(data);
        });
}

I was expecting the product-table (table I created to return the values). I list my products through the category entered by select, but it returns the initial list of the index.

Product-table

<td>
    @foreach($prod->category as $categoria)
    {{$categoria->erp_name}}
    @endforeach
</td>

Controller

public function search(Request $request)
{    
 $product = Product::query();
 $categoria = Category::query();
if ($request->isMethod('post'))
{
$data = json_decode($request->search_data);
$categoria;
$categoria = $data->categoria;
session(['categoria' => $categoria]);
if(strlen(session('categoria')) > 0)
{
 $product_ids = Product::whereHas('category', function ($query){
   $query->where('erp_category.erp_categoryid', '=', session('categoria'));
   })->get();
    $ids = [];

          foreach($product_ids as $product_data)
          {
              $ids[] = $product_data->erp_productid;
          }

          $product = $product->whereIn('erp_category.erp_categoryid', $ids);
      }
        $content = $product->paginate(10);
        $cat = Category::all();

        if ($request->isMethod('post'))
        {
            return view('admin.product-table')->with('product', $content)->with('cat',$cat);
        } else
        {
            return view('admin/product')->with('product', $content)->with('cat',$cat);
        }

Any suggestion?

Where are you using search_data to search after the post call? — Laerte
@Laerte I forgot to put my controllers. I already fixed it. Sorry. — Vinícius
If you dd($content); the results are what you are expecting? — Laerte
@Laerte My dd returned data from the start table, without organizing through the category. — Vinícius
If you dd($product_ids); before $ids = []; what are the results? The whereHas query seems to be right, but maybe it is not going in the if clause and paginating an empty query. — Laerte

Laerte Laerte · Accepted Answer · 2017-05-04T13:59:59

Try to change your code to something like this:

$content = null;
if(strlen(session('categoria')) > 0) {
    $product_ids = Product::whereHas('category', function ($query){
        $query->where('erp_category.erp_categoryid', '=', session('categoria'));
    });
    $content = $product_ids->paginate(10);
    $cat = Category::all();
}

if ($request->isMethod('post')) {
    return view('admin.product-table')->with('product', $content)->with('cat',$cat);
} else {
    return view('admin/product')->with('product', $content)->with('cat',$cat);
}

And then the view:

@if ($product !== null)
    @foreach($product as $p)
        <td>
            @foreach($p->category as $categoria)
                {{$categoria->erp_name}}
            @endforeach
        </td>
    @endforeach
@endif

The problem in the controller must be happening when you call whereIn or something like this. Probably you don't need to query Produto again.

The second thing is that you are not using the results in the view. You are calling the relationship function.

Try this and check if it helps...

How to filter a belongsToMany relationship? Laravel 5.4

1 Answers