With PHP Laravel, how can I make an Eloquent query that is conditional like a "when" statement, but dependent on the value of retrieved columns?

user_12826430 0
Viewed 130

This question can probably be best asked in the form of an analogy.

Let's say I have a "table" model that has many "filters" - these filters have a column "type" which has to either be "color" or "make", and an "allowed" column which contains the array of allowed values for each. I want to use these tables to display a filtered list of "cars" (which have color/make columns), and if I then add a car to the DB, I want it to figure out the set of tables to which that car needs to be added depending on every table's filters for car and make. So that means I query tables, and I join filters - but where the filters are "color", they have to contain the color of the car, and where the filters are "make", they have to match the make of the car. In this way, the table would get a list of cars that matches all of its filters. In pseudocode, this would be something like:

Table::join('filters', 'filters.table_id', '=', 'tables.id')
    ->when('filters.type', '=', "color", function($query) use ($car) {
        $query->whereJsonContains('filters.allowed', $car->type);
    })
    ->orWhen('filters.type', '=', "make", function($query) use ($car) {
        $query->whereJsonContains('filters.allowed', $car->make);
    })
    ->get();

And I'm trying to work out what, if possible, the correct way of writing such a query would be. I had the following before:

Table::join('filters', 'filters.table_id', '=', 'tables.id')
    >where(function ($query) use ($car) {
        $query->where('filters.type', "color")
            ->whereJsonContains('filters.allowed', $car->color);
    })->orWhere(function($query) use ($car) {
        $query->where('filters.type', "make")
            ->whereJsonContains('filters.allowed', $car->make);
    })->get();

But this would return all tables where the car matched any of the filters instead of all of them. If I make it a where instead of an orWhere, then I get conflicting conditions in where('filters.filter_type', "make") and where('filters.filter_type', "color"), which will then give me no results at all. So, is it possible to write conditional when clauses that depend upon the value of columns like in my pseudocode example? Thanks very much!

eloquent laravel mysql php
1 Answers

Figured it out! Wasn't actually as difficult as I thought, and didn't require any raw SQL. First, I created two extra relationships for the table in addition to the filters one:

public function filters()
{
    return $this->hasMany(Filter::class);
}

public function colorFilters()
{
    return $this->filters()->where('type', "color");
}

public function makeFilters()
{
    return $this->filters()->where('type', "make");
}

Then I was able to make the query work by using whereHas and logical grouping:

Table::where(function ($query) use ($car) {
    $query->whereHas('colorFilters', function($colorQuery) use ($car) {
        $colorQuery->whereJsonContains('allowed', $car->color);
    })
    ->orWhereDoesntHave('colorFilters');
})
->where(function ($query) use ($car) {
    $query->whereHas('makeFilters', function($makeQuery) use ($car) {
        $makeQuery->whereJsonContains('allowed', $car->make);
    })
    ->orWhereDoesntHave('makeFilters');
})
->get();
user_128264300
Related