CRUD Filters, Query translation into simple filter?

[gsc89]

Hi all,

I am trying to do a simple filter which will return results.

At the moment the model I am using has 3 appended values one of which is "free_places" I have tried to use:
$this->crud->addClause('where', 'free_places', '>', 0);

This doesn't work I get an error about free_places not being a field in the table (and it isn't) but it is set via a model attribute and I am able to use this as a column value within the crud itself just not on the filter.

So the next thing I tried was to set the filter up to do a join query within the filter itself using the similar code that the model function uses but this isn't so simple because this is a raw DB query as eloquent doesn't offer whats needed to do this with eloquent.

So my question is I have been able to get the results I would want to be displayed by the table using the following query.

$flights = Flight::select('flights.*')
                 ->leftJoin('bookings', 'flights.id', '=', 'bookings.flight_id')
                 ->selectRaw('COALESCE(sum(bookings.places_booked),0) as places_booked')
                 ->groupBy('flights.id')
                 ->havingRaw('COALESCE(sum(bookings.places_booked),0) < flights.max_passengers')
                 ->with('bookings')
                 ->get();

But how could I attach this to a filter for use with the crud?

If anyone can assist I would appreciate it.

[lloy0076]

If you mean https://laravel-backpack.readme.io/docs/filters#section-adding-a-filter then I'd add a model function that does what you need (you've already written it above) and then:

$this->crud->addFilter([ // add a "simple" filter called Flights
  'type' => 'simple',
  'name' => 'draft',
  'label'=> 'Draft'
],
false, // the simple filter has no values, just the "Draft" label specified above
function() { 
    $this->crud->query = $this->crud->query->where('draft', '1'); 
    // PUT THE COMPLICATED LOGIC INSTEAD OF THAT ^^ //
});

In other words, $this->crud->query should get the Query Builder(i.e. this) connected with the configured model for $this->crud.

[gsc89]

Thanks! I was about to type a long reply about why it wasn't working but then I realised what you were trying to explain to me and it works like so:

    // Free Spaces.
    $this->crud->addFilter(
        [ // simple filter
            'type'  => 'simple',
            'name'  => 'space_available',
            'label' => 'Space Available'
        ],
        false,
        function () {
            // if the flight has space available
            $this->crud->query = $this->crud->query->select('flights.*')
                             ->leftJoin('bookings', 'flights.id', '=', 'bookings.flight_id')
                             ->selectRaw('COALESCE(sum(bookings.places_booked),0) as places_booked')
                             ->groupBy('flights.id')
                             ->havingRaw('COALESCE(sum(bookings.places_booked),0) < flights.max_passengers')
                             ->with('bookings');
        }
    );

Again thanks a lot for all the help.