Dependant select2 with ajax

[pxpm]

          Good afternoon @tabacitu ,

How do I use this same structure in Backpack 6.x? I have a table of States and another of Cities and I need the city combo to depend on the state information.

On Controller:

[
     'name' => 'state_id',
     'label' => 'Estado',
    'type' => 'select2',
    'entity' => 'state', // the method that defines the relationship in your Model
    'model' => "App\Models\State", // foreign key model
    'attribute' => 'uf_name', // foreign key attribute that is shown to user
    'allows_null' => true,
    'placeholder' => "Selecione o Estado",
    'options' => (function ($query) {
          return $query->orderBy('uf', 'ASC')->get();
    }),
],
[ // select2_from_ajax: 1-n relationship
    'name' => 'city_id',
    'label' => 'Município', // Table column heading
    'type' => 'select2_from_ajax',
    'ajax' => false,
    'model' => "App\Models\City", // foreign key model
    'entity' => 'city', // the method that defines the relationship in your Model
    'attribute' => 'name', // foreign key attribute that is shown to user
    'data_source' => url('api/city'), // url to controller search function (with /{id} should return model)
    'method' => 'GET', // route method, either GET or POST
    'allows_null' => true,
    'placeholder' => 'Selecione o município', // placeholder for the select
    'minimum_input_length' => 0, // minimum characters to type before querying results
    'dependencies' => ['state_id'],
],

On City Api:

    public function index(Request $request)
    {
        $search_term = $request->input('q');
        $form = collect($request->input('form'))->pluck('value', 'name');

        $options = City::query();

        // if no category has been selected, show no options
        if (!$form['state_id']) {
            return [];
        }

        // if a category has been selected, only show articles in that category
        if ($form['state_id']) {
            $options = $options->where('state_id', $form['state_id']);
        }

        if ($search_term) {
            return $options->where('name', 'ilike', '%' . strtoupper($search_term) . '%')
                ->orderBy('name')
                ->paginate(10);
        }

        return $options->paginate(10);
    }
    public function show($id)
    {
        return City::find($id);
    }

Return Error: Undefined array key "state_id"

Could you help me with this problem or give me a cool alternative?

Originally posted by @helesjunior in Laravel-Backpack/CRUD#1484 (comment)

[pxpm]

That specific error could be fixed if you use in the if:

if(! isset($form['state_id']) || empty($form['state_id']))

Cheers

[helesjunior]

Good morning Peter @pxpm ,

Thank you for creating an issue to address my problem. The solution presented resolved the error, but did not solve the dependency problem. Could you give me an example of how to retrieve, with "FetchOperation", the value of another combo using dependencies?

I took the example from the documentation, but I can't get the value of the state_id field to filter the items that will be presented in the Cities combo.

    protected function fetchCity()
    {
        return $this->fetch([
            'model' => \App\Models\City::class,
            'searchable_attributes' => ['state_id', 'name'],
            'paginate' => 10, // items to show per page
            'searchOperator' => 'LIKE',
            'query' => function($model) {
                return $model->where('state_id', $model->state_id); //How to get the select value in the states combo?
            } // to filter the results that are returned
        ]);
    }

Tks.

[helesjunior]

@pxpm

Oops, I managed to solve the problem. I believe it would be good if they added this to the documentation. You need to add the parameter "'include_all_form_fields' => true" for it to work. So it ended up like this:

In Field

           [ // select2_from_ajax: 1-n relationship
                'name' => 'city_id',
                'label' => 'Município', // Table column heading
                'type' => 'relationship',
                'ajax' => true,
                'model' => "App\Models\City", // foreign key model
                'entity' => 'city', // the method that defines the relationship in your Model
                'attribute' => 'name', // foreign key attribute that is shown to user
                'data_source' => url('/provider/fetch/city'), // url to controller search function (with /{id} should return model)
                'method' => 'POST', // route method, either GET or POST
                'allows_null' => true,
                'placeholder' => 'Selecione o município', // placeholder for the select
                'minimum_input_length' => 0, // minimum characters to type before querying results
                'dependencies' => ['state_id'],
                **'include_all_form_fields' => true**
//                'tab'                  => 'Selects',
//                'wrapperAttributes'    => ['class' => 'form-group col-md-4'],
            ],

In Fetch Fuction:

    protected function fetchCity()
    {
        return $this->fetch([
            'model' => \App\Models\City::class,
            'searchable_attributes' => [],
            'query' => function($model) {
                $search = request()->input('q') ?? false;
                $form = collect(request()->input('form'))->pluck('value', 'name');

                if (! isset($form['state_id']) || empty($form['state_id'])) {
                    return $model->where('state_id', 0);
                }

                if($search)
                {
                    return $model->whereRaw('LOWER(cities.name) LIKE "%' . strtolower($search) . '%"')->where('state_id', $form['state_id']);
                }else{
                    return $model->where('state_id', $form['state_id']);
                }

            }
        ]);
    }

And de results:

Thanks a lot.

[pxpm]

Ahh sorry I missed it. 🙏

Some remarks tho: it does not necessarily need to be true. true will send ALL fields on the form, so a bigger request/payload. If you only need one or two fields you can specify them: include_all_form_fields => ['field_needed_in_backend', 'other_neeeded_field]'

I'd recommend you give a look at: https://backpackforlaravel.com/docs/6.x/crud-how-to#how-to-add-a-relationship-field-that-depends-on-another-field

We have the explanation for include_all_form_fields, backpack_form_input() helper etc.

Cheers

[helesjunior]

Good morning @pxpm ,

It's me again.

A question still arose for this same Select2 field with dependency. When I use the "repeatable" type field in the fetch function I receive an array of arrays with the items in the repeatable field. How can I know the ID of the item that came in the request? Because the backpack_form_input() function returns all repeatable items.

Like this:

    protected function fetchTuss()
    {
        return $this->fetch([
            'model' => \App\Models\Tuss::class,
            'searchable_attributes' => [],
            'query' => function ($model) {
                $search = request()->input('q') ?? false;
                $form = backpack_form_input();
                if (!isset($form['item'][???]['tuss']) || empty($form['item'][???]['tuss'])) {
                    return $model->whereIn('tuss_id', []);
                }

                if ($search) {
                    return $model->whereRaw('LOWER(cbo.description) LIKE "%' . strtolower($search) . '%"')
                        ->whereIn('tuss_id', $form['item'][???]['tuss'])
                        ->orderBy('description', 'ASC');
                } else {
                    return $model->whereIn('tuss_id', $form['item'][???]['tuss'])
                        ->orderBy('description', 'ASC');
                }

            }]);

Tks.

Heles Jr.

[pxpm]

Hey @helesjunior

Repeatable also send a special key called: triggeredBy alongside the form.
Inside you will have the field and the row that triggered the change.

Let me know if that helps.

Cheers

[helesjunior]

Hi @pxpm,

Thank you so much.
Worked perfectly. That's why I love Backpack for laravel, because it's versatile and has a wonderful team behind it. Congratulations.

I will share the code to help other users of the tool.

    protected function fetchCbo()
    {
        return $this->fetch([
            'model' => \App\Models\Cbo::class,
            'searchable_attributes' => [],
            'query' => function ($model) {
                $search = request()->input('q') ?? false;
                $row = request()->triggeredBy['rowNumber'];
                $form = backpack_form_input();

                if (!isset($form['items'][$row]['tuss']) || empty($form['items'][$row]['tuss'])) {
                    return $model->where('tuss_id', []);
                }

                if ($search) {
                    return $model->whereRaw('LOWER(cbo.description) LIKE "%' . strtolower($search) . '%"')
                        ->where('tuss_id', $form['items'][$row]['tuss'])
                        ->orderBy('description', 'ASC');
                } else {
                    return $model->where('tuss_id', $form['items'][$row]['tuss'])
                        ->orderBy('description', 'ASC');
                }

            }]);
    }

Tks!!

[pxpm]

Appreciate the kind words 😍

Thank you very much for sharing your solution, 100% someone will bump into this and be grateful to you 👍

Happy coding 🙏