Laravel Backpack CRUD entry list filtering based on related rows

0 Laravel Backpack CRUD entry list filtering based on related rows

Site allows members (people) to register for programs. People belong to one or
more Accounts. Programs are divided up into Seasons.

When viewing the entity list page for Accounts, we want to be able to filter the
list based on the seasons the people within each account are registered for.
E.g., we often want to show only Accounts which have People registered for
Programs within the current Season.

Pertinent database tables and columns:
accounts: id
people: id
account_person: account_id, person_id
seasons: id, name
programs: id, season_id
registrations: person_id, program_id

Laravel v10
Backpack v5

1 app-Http-Controllers-Admin-AccountCrudController.php

<?php
namespace App\Http\Controllers\Admin;

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

class AccountCrudController extends CrudController
{
    use \Backpack\CRUD\app\Http\Controllers\Operations\ListOperation;

    protected function setupListOperation()
    {
        $that = $this;
        $filterSetup = function($value = null) use($that) {
            if (!empty($_GET)) {
                // change from 'select * from...' to 'select accounts.* from...'
                $this->crud->query->select('accounts.*');

                // pull in data needed for season filter
                // if you need data for accounts that don't have joined data, use leftJoin instead of join
                $this->crud->query->join('account_person', 'account_person.account_id', '=', 'accounts.id');
                $this->crud->query->join('people', 'people.id', '=', 'account_person.person_id');
                $this->crud->query->join('registrations', 'registrations.person_id', '=', 'people.id');
                $this->crud->query->join('programs', 'programs.id', '=', 'registrations.program_id');

                // get rid of the extraneous rows due to the joins
                $this->crud->query->groupBy('accounts.id');
            }
        };
        $this->crud->addFilter(
            array(
                'name' => 'filters_setup',
                'type' => 'hidden',
            ),
            false,
            $filterSetup, // called if the filter is active (which it shoulnd't be since it's hidden)
            $filterSetup // called if the filter is not active
        );
        $this->crud->addFilter(
            array(
                'name'  => 'season',
                'type'  => 'dropdown',
                'label' => 'Season',
            ),
            \App\Models\Season::getSeasonList(), // get seasons as an array of id => name
            function($value) {
                $this->crud->query->where(function($query) use($value) {
                    $this->crud->query->where('programs.season_id', '=', $value);
                });
            }
        );
        // other filters and columns for display here...
    }
    // other CRUD code here...
}

2 resources-views-vendor-backpack-crud-filters-hidden.blade.php

{{-- Nothing to display, used for filter setup --}}