Advance where orWhere query, looping over array.

I'm having some trouble building an advance where() orWhere() query, where the orWhere() loops over array of filters. I am using Livewire for the search filter but will just add some sample content here.

Sample data:

$locations = [
  	{'id': 1, 'category_id': 1, 'min_value':  10, 'max_value': 20},
	{'id': 2, 'category_id': 2, 'min_value':  15, 'max_value': 25},
	{'id': 3, 'category_id': 2, 'min_value':  20, 'max_value': 30},
	{'id': 4, 'category_id': 3, 'min_value':  10, 'max_value': 15},
    {'id': 5, 'category_id': 3, 'min_value':  12, 'max_value': 26}
];

In my livewire controller I would have something like:

public $category_id;
public $values = [];

public function getLocationsFromDB() {
    $locations= DB::table('locations');
    if ($this->category_id) {
        $locations
                ->join('categories', 'location.category_id', '=', 'categories.id')
                ->where('categories.id', $this->category_id);
    }
    if (count($this->values)) {
        $array = $this->values;
        // foreach($array as $item) {
 		    // ->where('min_value', '<=', $item)
			// ->where('max_value', '>=', $item)
        // }
 	    // OR WHERE 2nd $item
        // OR WHERE 3rd $item
        // ....
	}
}

public function render() {
    return view('livewire.testcomponent', [
	    'locations' => $this->getLocationsFromDB
	]);
}

So I need to create an orWhere() for each item in $values array. Each item individually has 2x where (resulting in where AND where).

Thanks for your insights.

MitchellStarc

4 years ago

Level 1

I am running into a semi-common problem that others have when using the new Tailwind which has the JIT compiler enabled by default. https://omegle.onl/ https://xender.vip/ Tailwind now scans your views for classnames during the build step, and will tree-shake unused classes from your CSS file. In theory this is great, but you lose access to dynamic class names.

tvbz

4 years ago

Level 4

@MitchellStarc How is this relevant to my topic? Please remove it and move to dedicated topic if need be.

MichalOravec

4 years ago

Best Answer

Level 75

public function render()
{
    return view('livewire.testcomponent', [
	    'locations' => $this->getLocationsFromDB()
	]);
}

protected function getLocationsFromDB()
{
    return DB::table('locations')->when($this->category_id, function ($query, $categoryId) {
        return $query->join('categories', 'location.category_id', '=', 'categories.id')
            ->where('categories.id', $categoryId);
    })->when($this->values, function ($query, $values) {
        return $query->where(function ($query) use ($values) {
            foreach ($values as $item) {
                $query->orWhere(function ($query) use ($item) {
                    $query->where('min_value', '<=', $item)
                        ->where('max_value', '>=', $item);
                });
            }
        });
    })->get();
}

https://laravel.com/docs/8.x/queries#conditional-clauses

https://laravel.com/docs/8.x/queries#logical-grouping

2 likes

tvbz

4 years ago

Level 4

@MichalOravec Thanks! The logical grouping was indeed what I needed.

Question: Is there a recommendation using when() over if() in terms of performance? Personally I like the if more, probably because the when is new to me and ifs are very readable. :)

Thanks!

Sinnbeck

4 years ago

Level 102

@tvbz when() is just an if statement

This is the complete code

public function when($value, $callback, $default = null)
    {
        if ($value) {
            return $callback($this, $value) ?: $this;
        } elseif ($default) {
            return $default($this, $value) ?: $this;
        }

        return $this;
    }

2 likes

Please or to participate in this conversation.