laravel query builder

Try add a form with action=GET and then name your input like filter[brand] and filter[category]

Without your JS code should works. But I don't see issue even in your JS code, does url change when you click filter? It change correctly. I didn't check all your models relations, but seem that brand_id and product_id are not in products table. In this case you need to make a ->join() with brand and category and attributes, then your filter brand_id and category_id can be accessed using dots, see https://spatie.be/docs/laravel-query-builder/v2/features/filtering#exact-or-partial-filters-for-related-properties

thank you very very much @thewebartisan7 I'm tried what you suggested and it works with brand but when I check 2 brands it return just the products of the last or first brand I check not both and return the ids of the two brands

http://localhost/site/public/products?filter%5Bbrand%5D=1&filter%5Bbrand%5D=2

and for category if I check one or two of them it return this (without using join)

Illuminate \ Database \ QueryException (42S22)
SQLSTATE[42S22]: Column not found: 1054 Champ 'category_id' inconnu dans where clause (SQL: select * from `products` where `category_id` = 2)
Previous exceptions
SQLSTATE[42S22]: Column not found: 1054 Champ 'category_id' inconnu dans where clause (42S22)

and when I use (join) I don't know how to write this exactly it return error also

 ->join('categories', 'product.id', 'categories.product_id')
            ->allowedFilters([
                AllowedFilter::exact('brand', 'brand_id'),
                AllowedFilter::exact('category', null, $addRelationConstraint),
                ])
            ->get();

Illuminate \ Database \ QueryException (42S22)
SQLSTATE[42S22]: Column not found: 1054 Champ 'category' inconnu dans where clause (SQL: select * from `products` inner join `categories` on `product`.`id` = `categories`.`product_id` where `category` = 2)
Previous exceptions
SQLSTATE[42S22]: Column not found: 1054 Champ 'category' inconnu dans where clause (42S22)

category id

http://localhost/site/public/products?filter%5Bcategory%5D=2

so how to change that for attributes(size,color) also and thank you very much

For multiple values of the same filter you need to use comma separator, example:

?filter[brand]=1,2,3,4

Or also this way:

?filter[brand][]=1&filter[brand][]=2

The first way separated by comma, you may need JS to concat that string. But the second way you can just create the input using multi dimensional array like so:

<input name="filter[brand][]" value=1/>

Regarding join, your join seem correct, you could try also with

$products = QueryBuilder::for(Product::class)
           ->with('categories')
           >with('brands')
            ->allowedFilters([
                AllowedFilter::exact('brand', 'brand_id'),
                AllowedFilter::exact('category', 'category_id'),
                ])
            ->get();

See here about relationship https://laravel.com/docs/8.x/eloquent-relationships

thank you @thewebartisan7 nothing change same error and about brand I mean that when I check two brands and click filter it return just the products of the last or first brand I check (not both) I check zara and hm it reurn products of hm or zara not both

Try paste here updated code, only controllers and view

thank you @thewebartisan7 I used exact for brand like this

                AllowedFilter::exact('brand', 'brand_id'),

and for category code in productcontroller

AllowedFilter::custom('category', new FiltersCategories),

productController:

 $products = QueryBuilder::for(Product::class)
          
            ->allowedFilters([
                AllowedFilter::exact('brand', 'brand_id'),
                AllowedFilter::custom('category', new FiltersCategories),
                
                ])
            ->get();
        return view('site.pages.products', compact('products'));

and code in FiltersCategories.php

<?php

namespace App\Filters;

use Spatie\QueryBuilder\Filters\Filter;
use Illuminate\Database\Eloquent\Builder;

class FiltersCategories implements Filter
{
    public function __invoke(Builder $query, $value, string $property) : Builder
    {
        return $query->whereHas('categories', function ($query) use ($value) {
            if (is_array($value)) {
                return $query->whereIn('category_id', $value);
            }

            return $query->where('category_id', $value);
        });
    }
}

products.blade.php

       <p>Brands</p>
                <form method="get" action="{{ route('products.index') }}"> 
@foreach ($brands as $brand)
    <label class="m-checkbox">
        <input
            name="filter[brand]" type="checkbox" value="{{ $brand->id }}"
            @if (in_array($brand->id, explode(',', request()->input('filter.brand'))))
                checked
            @endif
        >
        {{ $brand->name }}
    </label>
@endforeach

<button type="submit">apply filter</button>
</form>
<p>Categories</p>
<form method="get" action="{{ route('products.index') }}"> 
@foreach ($categories as $category)
@if ($category->id!=1)
    <label>
        <input
            name="filter[category]" type="checkbox" value="{{ $category->id }}"
            @if (in_array($category->id, explode(',', request()->input('filter.category'))))
                checked
            @endif
        >
        {{ $category->name }}
    </label>
    @endif
@endforeach

<button type="submit">apply filter</button>

so when I did this It works for brand and category and get products belong to them but It show me just products belong to one Brand and one category if I check in by brand adidas and gucci it show me just products belong to adidas and the same for by category this is image: https://i.imgur.com/5Zm5TN9.png but when I type this

localhost/site/public/products?filter[brand]=1,6

this is image: https://i.imgur.com/xtCx1Hr.png it show me products belong to id=1(gucci) and id=6(adidas) thats mean it works with this url and it works for by category also

so how to fix my problem and get products like when I type this "localhost/site/public/products?filter[brand]=1,6" and when I add this

name="filter[brand][]"

it gives me error:

ErrorException (E_ERROR)
explode() expects parameter 2 to be string, array given (View: C:\wamp\www\site\resources\views\site\pages\products.blade.php)

thank you

If you use filter[brand] in loop, then it will always override single item in array, for this reason you can search only by one brand and one category.

You need to use name="filter[brand][]" this will create a multidimensional array with all brands IDs.

So when you use name="filter[brand][]" then request()->input('filter.brand')) it's already an array, so you don't need to use explode.

Your foreach should works in this way:

@foreach ($brands as $brand)
    <label class="m-checkbox">
        <input
            name="filter[brand][]" type="checkbox" value="{{ $brand->id }}"
            @if ( in_array( $brand->id,  request()->input('filter.brand') )
                checked
            @endif
        >
        {{ $brand->name }}
    </label>
@endforeach

in case you want to support both filters, as string like ?filter[brand]=1,2,3 and as array like ?filter[brand][]=1&filter[brand][]=2 then you can check if filter.brand is array or not, and then explode it or not.

Like:

@foreach ($brands as $brand)
    <label class="m-checkbox">
        <input
            name="filter[brand][]" type="checkbox" value="{{ $brand->id }}"

@if(is_array(request()->input('filter.brand'))
            @if ( in_array( $brand->id,  request()->input('filter.brand') )
                checked
            @endif
@else
           @if (in_array($brand->id, explode(',', request()->input('filter.brand'))))
                checked
            @endif
@endif

        >
        {{ $brand->name }}
    </label>
@endforeach

thank you very much @thewebartisan7 I wish all your dreams come true thank you very very much

ok when I tried your second way It works and that's very good (for by brand and by category) but I think there's something wrong because In last code when I tell you that it show just one brand and just one category , when I check forexepmple adidas in by brand and click filter and basket in by category and click filter it gives me url like this

localhost/site/public/products?filter%5Bbrand%5D=9&filter%5Bcategory%5D=16

and it gives me the products of brand ([brand]=9) but with this code I click filter for brand it gives me url like this

localhost/site/public/products?filter%5Bbrand%5D=9

if I click filter for category get this url (I know it's correct but not the same like the last code as I explained)

localhost/site/public/products?filter%5Bcategory%5D=16

I'm confused if this is wrong or not because I know that when I check category Men/basket and check adidas It should show me adidas for men not for woman or all adidas products like this

localhost/site/public/products?filter[brand]=adidas&filter[category]=basket

and when I tried your first way it gives me error when I check category with id=16 this is image for error https://i.imgur.com/Mo3BSBR.png and when check brand with id=8 this is image for error https://i.imgur.com/DDwRP6d.png and why still show me url like this

localhost/site/public/products?filter%5Bcategory%5D=16

and not like this

localhost/site/public/products?filter[category][]=16

but I tried first way with by brand (before add category code) and it works the same as the second way please if you can explain more maybe with image or anything I can't get the difference between the two ways and thank you very much

I didn't understand everything, but as I understand correctly, it's best that you need to know which query is executed for each request, and then starting from that you should understand how filters works.

There is several way to logs query, this package is great tool which allow you to see every query executed in a page, see https://github.com/barryvdh/laravel-debugbar

Otherwise you can add in AppServiceProvider, boot method, this code:

    public function boot()
    {
//        // Add in boot function
        \DB::listen(function($query) {
            \File::append(
                storage_path('/logs/query.log'),
                '[' . date('Y-m-d H:i:s') . ']' . PHP_EOL . $query->sql . ' [' . implode(', ', $query->bindings) . ']' . PHP_EOL . PHP_EOL
            );
        });
    }

This will log all queries, inside file /logs/query.log

Once you have this, start first check directly via URLs all filters combination how it works.

There is two way to use filters, using multidimensional array or just by passing multipe values with comma separator.

So try this:

1. Multidimension array:

localhost/site/public/products?filter[category][]=16&filter[category][]=12&filter[category][]=17

1. Multiple values with comma separator:

`localhost/site/public/products?filter[category]=16,12,17

Both this URLs should give the same result.

If this doesn't help you to understand, let me know.

I use often this package and works great, if you setup correctly your model relations and the filters, then it should works as explained above. if something not works, then double check relations and how you setup filters. Starting from generated queries.

thank you @thewebartisan7 and I wish all your dreams come true sorry for this mistakes but when I ask someone it's more easier to understand code and errors I will learn and try to improve myself and learn from my mistakes

maybe I can't explain that's why you don't understand me and if I don't understand something I will let you know and thank you very very much

but what about this error when I tried your first way it gives me error when I check category with id=16 this is image for error https://i.imgur.com/Mo3BSBR.png and when check brand with id=8 this is image for error https://i.imgur.com/DDwRP6d.png

Error message should be clear.

Because you are using php function in_array, which expect second parameter to be an array, but you are passing a string.

I suggested in my previous post to check before if filter.category or filter.brand is an array using:

@if(is_array(request()->input('filter.brand'))
            @if ( in_array( $brand->id,  request()->input('filter.brand') )
                checked
            @endif
@else
           @if (in_array($brand->id, explode(',', request()->input('filter.brand'))))
                checked
            @endif
@endif

But in your screenshot, I don't see this check.

Did I explain myself?

Also, you can pass as second parameter to request()->input() a default value, because in your screenshot seem NULL. This happen if there are not filters.

So you can:

request()->input('filter.brand', [])

Default value empty array.

Full code:

@if(is_array(request()->input('filter.brand'))
            @if ( in_array( $brand->id,  request()->input('filter.brand') )
                checked
            @endif
@else
           @if (in_array($brand->id, explode(',', request()->input('filter.brand', []))))
                checked
            @endif
@endif

Or also just check if filter.brand exist:

@if(request()->has('filter.brand')

// Filters exist
@endif

thank you @thewebartisan7

(I have some problem with package "laravel debugbar" I will try to fix it)

when I type this url:

localhost/site/public/products?filter[category][]=16&filter[category][]=12&filter[category][]=15

https://i.imgur.com/V9IkoQQ.png

query.log

[2020-10-02 20:47:31]
select column_name as `column_name` from information_schema.columns where table_schema = ? and table_name = ? [site, settings]

[2020-10-02 20:47:31]
select * from `settings` []

[2020-10-02 20:47:32]
select * from `attribute_values` []

[2020-10-02 20:47:32]
select * from `products` where exists (select * from `categories` inner join `product_categories` on `categories`.`id` = `product_categories`.`category_id` where `products`.`id` = `product_categories`.`product_id` and `category_id` in (?, ?, ?)) [16, 12, 15]

[2020-10-02 20:47:32]
select * from `categories` order by -name ASC []

[2020-10-02 20:47:32]
select * from `brands` order by -name ASC []

[2020-10-02 20:47:32]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [1]

[2020-10-02 20:47:32]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [2]

and when type this url:

localhost/site/public/products?filter[category]=16,12,15

https://i.imgur.com/IAxtgwp.png

query.log

[2020-10-02 20:49:23]
select column_name as `column_name` from information_schema.columns where table_schema = ? and table_name = ? [site, settings]

[2020-10-02 20:49:23]
select * from `settings` []

[2020-10-02 20:49:23]
select * from `attribute_values` []

[2020-10-02 20:49:23]
select * from `products` where exists (select * from `categories` inner join `product_categories` on `categories`.`id` = `product_categories`.`category_id` where `products`.`id` = `product_categories`.`product_id` and `category_id` in (?, ?, ?)) [16, 12, 15]

[2020-10-02 20:49:24]
select * from `categories` order by -name ASC []

[2020-10-02 20:49:24]
select * from `brands` order by -name ASC []

[2020-10-02 20:49:24]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [1]

[2020-10-02 20:49:24]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [2]

and when i type this for brand :

http://localhost/site/public/products?filter[brand]=1,6,9

query.log

[2020-10-02 20:51:11]
select * from `products` where `brand_id` in (?, ?, ?) [1, 6, 9]

and the same result for this

http://localhost/site/public/products?filter[brand]=1,6,9

but when I type this:

http://localhost/site/public/products?filter%5Bbrand%5D=1&filter%5Bbrand%5D=6&filter%5Bbrand%5D=9

https://i.imgur.com/pTlEdQd.png

query.log

[2020-10-02 20:56:03]
select * from `products` where `brand_id` = ? [9]

and when I type this

http://localhost/site/public/products?filter%5Bcategory%5D=16&filter%5Bcategory%5D=12&filter%5Bcategory%5D=15

query.log

[2020-10-02 21:06:21]
select * from `products` where exists (select * from `categories` inner join `product_categories` on `categories`.`id` = `product_categories`.`category_id` where `products`.`id` = `product_categories`.`product_id` and `category_id` = ?) [15]

ok I tell you that when I tried your second way It works when check category with id=12,15,16 with checkbox query.log:

[2020-10-02 21:18:35]
select column_name as `column_name` from information_schema.columns where table_schema = ? and table_name = ? [site, settings]

[2020-10-02 21:18:35]
select * from `settings` []

[2020-10-02 21:18:35]
select * from `attribute_values` []

[2020-10-02 21:18:36]
select * from `products` where exists (select * from `categories` inner join `product_categories` on `categories`.`id` = `product_categories`.`category_id` where `products`.`id` = `product_categories`.`product_id` and `category_id` in (?, ?, ?)) [12, 15, 16]

[2020-10-02 21:18:36]
select * from `categories` order by -name ASC []

[2020-10-02 21:18:36]
select * from `brands` order by -name ASC []

[2020-10-02 21:18:36]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [1]

[2020-10-02 21:18:36]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [2]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [1]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [2]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [4]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [7]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [8]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [10]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [11]

[2020-10-02 21:18:36]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [12]

[2020-10-02 21:18:36]
select * from `categories` order by -name ASC []

and this when I type this http://localhost/site/public/products?filter[category][]=16&filter[category][]=12&filter[category][]=15 query.log:

[2020-10-02 21:21:20]
select column_name as `column_name` from information_schema.columns where table_schema = ? and table_name = ? [site, settings]

[2020-10-02 21:21:21]
select * from `settings` []

[2020-10-02 21:21:21]
select * from `attribute_values` []

[2020-10-02 21:21:21]
select * from `products` where exists (select * from `categories` inner join `product_categories` on `categories`.`id` = `product_categories`.`category_id` where `products`.`id` = `product_categories`.`product_id` and `category_id` in (?, ?, ?)) [16, 12, 15]

[2020-10-02 21:21:22]
select * from `categories` order by -name ASC []

[2020-10-02 21:21:22]
select * from `brands` order by -name ASC []

[2020-10-02 21:21:22]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [1]

[2020-10-02 21:21:22]
select * from `attribute_values` where `attribute_id` = ? order by -value ASC [2]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [1]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [2]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [4]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [7]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [8]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [10]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [11]

[2020-10-02 21:21:22]
select * from `product_images` where `product_images`.`product_id` = ? and `product_images`.`product_id` is not null [12]

[2020-10-02 21:21:22]
select * from `categories` order by -name ASC []