Crud: filter by pivot table

[rzaglinskij]

HI, could you please help me with this:
I have 3 tables: subscriber, category, subscriber_category. Third table is pivot table.
I need to have a filter by category.
I have:

$this->crud->addFilter([ // select2_multiple filter
            'name' => 'category',
            'type' => 'select2_multiple',
            'label'=> 'Category'
        ], function() {
            foreach(\App\Models\Category::get() AS $k=>$v){
                $res[$v->id] = $v->name;
            }
            return($res);
        }, function($values) {
            $values = json_decode($values);
            unset($values[0]);
             foreach ($values as $key => $value) {
                 $this->crud->addClause('with', ['category' => function ($query) use($value) {
                     $query->where('category_id', '=', $value);
                 }]);
             }
        });

and filter is not working.
Please help me.

[OwenMelbz]

Define not working?

[rzaglinskij]

this is not working
$this->crud->addClause('with', ['category' => function ($query) use($value) {
$query->where('category_id', '=', $value);
}]);

filtering.
I want to filter by category ID

[noxify]

Is there a error message or just an empty (data)table after selecting the filter?

[rzaglinskij]

there are no errors. just the same results in the table

[OwenMelbz]

Can you paste your whole crud controller please

[rzaglinskij]

code is here - http://pastebin.com/iQCetfxD

[OwenMelbz]

Ha erm, I think something went a little wrong there!

Could you paste the WHOLE thing, and make sure its in a readable format so people can see where the issue may lay :D

this as an example

```php

namespace App\Http\Controllers\Admin;

use Backpack\CRUD\app\Http\Controllers\CrudController;

// VALIDATION: change the requests to match your own file names if you need form validation
use App\Http\Requests\GameRequest as StoreRequest;
use App\Http\Requests\GameRequest as UpdateRequest;

use App\Models\Season;

class GameCrudController extends CrudController
{

public function setUp()
{

    /*
    |--------------------------------------------------------------------------
    | BASIC CRUD INFORMATION
    |--------------------------------------------------------------------------
    */
    $this->crud->setModel("App\Models\Game");
    $this->crud->setRoute("admin/games");
    $this->crud->setEntityNameStrings('game', 'games');
    $this->crud->enableAjaxTable();

    /*
    |--------------------------------------------------------------------------
    | BASIC CRUD INFORMATION
    |--------------------------------------------------------------------------
    */

    // ------ CRUD FIELDS
    $this->crud->addField([
        'name' => 'name',
        'label' => 'Name',
        'type' => 'text',
        'attributes' => [
            'required' => 'required'
        ]
    ]);

    $this->crud->addField([
        'name' => 'icon',
        'label' => 'Icon',
        'type' => 'image',
        'upload' => true,
        'crop' => true,
        'aspect_ratio' => '1:1',
        'disk' => 'uploads',
        'hint' => 'Recommended 110px x 110px .svg or .png'
    ]);

    $this->crud->addField([
        'name' => 'colour',
        'label' => 'Colour',
        'type' => 'radio',
        'options' => [
            '#b90276' => '<span style="color: #b90276;">Pink</span>',
            '#009ab1' => '<span style="color: #009ab1;">Aqua</span>',
            '#7a2182' => '<span style="color: #7a2182;">Puple</span>',
            '#ff9900' => '<span style="color: #ff9900;">Yellow</span>',
            '#008842' => '<span style="color: #008842;">Green</span>',
            '#003366' => '<span style="color: #003366;">Navy</span>',
        ],
        'inline' => true,
        'attributes' => [
            'required' => 'required'
        ]
    ]);

    $this->crud->addField([
        'label' => 'Season',
        'default' => 1,
        'name' => 'season_id',
        'type' => 'select2',
        'entity' => 'season',
        'attribute' => 'name',
        'model' => "App\Models\Season"
    ]);

    $this->crud->addField([
        'label' => 'Position / Order',
        'name' => 'order',
        'type' => 'number',
    ]);

    $this->crud->addField([
        'label' => 'Schools can play?',
        'default' => 1,
        'name' => 'school_access',
        'type' => 'radio',
        'options' => [1 => 'Yes', 0 => 'No'],
        'inline' => true
    ]);

    $this->crud->addField([
        'label' => 'Public can play?',
        'default' => 1,
        'name' => 'public_access',
        'type' => 'radio',
        'options' => [1 => 'Yes', 0 => 'No'],
        'inline' => true
    ]);

    $this->crud->addField([
        'label' => 'Globally enabled?',
        'default' => 1,
        'name' => 'status',
        'type' => 'radio',
        'options' => [1 => 'Yes', 0 => 'No'],
        'inline' => true
    ]);


    // ------ CRUD COLUMNS
    $this->crud->addColumn([
        'name' => 'name',
        'label' => 'Name',
        'type' => 'text'
    ]);

    $this->crud->addColumn([
        'name' => 'icon',
        'label' => 'Icon',
        'type'  => 'image',
        'alt_column'  => 'name',
        'disk' => 'uploads'
    ]);

    $this->crud->addColumn([
        'name' => 'season_id',
        'label' => 'Season',
        'type'  => 'select',
        'entity' => 'season',
        'attribute' => 'name',
        'model' => 'App\Models\Season',
        'link'   => '/'.config('backpack.base.route_prefix').'/seasons/{id}/edit'
    ]);

    $this->crud->addColumn([
        'name' => 'order',
        'label' => 'Order',
        'type'  => 'number'
    ]);

    $this->crud->addColumn([
        'name' => 'school_access',
        'label' => 'School Access',
        'type'  => 'radio',
        'options'   => [0 => 'Inactive', 1 => 'Active']
    ]);

    $this->crud->addColumn([
        'name' => 'public_access',
        'label' => 'Public Access',
        'type'  => 'radio',
        'options'   => [0 => 'Inactive', 1 => 'Active']
    ]);

    $this->crud->addColumn([
        'name' => 'status',
        'label' => 'Status',
        'type'  => 'radio',
        'options'   => [0 => 'Inactive', 1 => 'Active']
    ]);

    $currentSeason = Season::where(['status' => 1])->first();

    if ($currentSeason) {
        $this->crud->addFilter([
            'type' => 'simple',
            'name' => 'active_season',
            'label' => 'Current Season Games'
        ], false, function () use ($currentSeason) {
            if ($currentSeason) {
                $this->crud->addClause('where', 'season_id', $currentSeason->id);
            } else {
                return redirect('admin/games');
            }
        });
    }

    $this->crud->addFilter(
        [
          'name' => 'season_id',
          'type' => 'dropdown',
          'label' => 'By Season'
        ],
        function () {
            $seasons = Season::all();
            $seasonList = [];
            $seasons->each(function ($s) use (&$seasonList) {
                $seasonList[$s->id] = $s->name;
            });

            return $seasonList;
        },
        function ($value) {
            $this->crud->addClause('where', 'season_id', $value);
        }
    );

    $this->crud->addButtonFromView('line', 'view', 'questions_button', 'end');
}

public function store(StoreRequest $request)
{
    return parent::storeCrud();
}

public function update(UpdateRequest $request)
{
    return parent::updateCrud();
}

public function gameOptions()
{
    $term = $this->request->input('term');
    $options = $this->crud->model->where('name', 'like', '%'.$term.'%')->get();
    return $options->pluck('game_season', 'id');
}

}

``

[rzaglinskij]

sorry...
edited - http://pastebin.com/iQCetfxD

[OwenMelbz]

try removing parent::__construct();

[rzaglinskij]

the same results

[rzaglinskij]

subscriber model - http://pastebin.com/prDz6xU2
category model - http://pastebin.com/m39vA0C4
crud controller - http://pastebin.com/4seRUjqW

[rzaglinskij]

Any ideas ?
I really need this feature ASAP...

[OwenMelbz]

I can't replicate any issues in any similar project.

I suggest you strip out all the code from the fields and basic columns to just 1 filter and 1 name column.

Then you can isolate if there is a conflict, user error or bug

[rzaglinskij]

could you, please post here an example of howto create filter on pivot table ?

[OwenMelbz]

Scroll up ^

[OwenMelbz]

as this might be an ajax issue, i'll migrate into the other thread

[rzaglinskij]

also, still not working
$this->crud->addFilter([ // select2 filter
'name' => 'frontendtags',
'type' => 'select2',
'label'=> 'FE Tag'
], function() {
return \App\Models\FrontendTags::all()->pluck('name', 'id')->toArray();
}, function($value) {
$this->crud->addClause('with', ['frontendtags' => function ($query) use ($value) {
$query->where('tag_id', '=', $value)->toSql();
}]);
});
how can I debug this generated SQL ?

[darrena092]

Not sure if you're still having this issue - but there's an example in the docs on how to filter based on a select2_multiple filter using an n-n relationship which has worked for me recently.

$this->crud->addFilter([ // select2_multiple filter
  'name' => 'tags',
  'type' => 'select2_multiple',
  'label'=> 'Tags'
], function() { // the options that show up in the select2
    return Product::all()->pluck('name', 'id')->toArray();
}, function($values) { // if the filter is active
    foreach (json_decode($values) as $key => $value) {
        $this->crud->query = $this->crud->query->whereHas('tags', function ($query) use ($value) {
            $query->where('tag_id', $value);
        });
    }
});

See: https://laravel-backpack.readme.io/v3.0/docs/filters#section-extra-examples