Is it possible to combine whereHas with 'or' queries?

I'm trying to implement a filter system where, among other attributes and relationships, items are categorized. I realized searching items by their categories alone returns insufficient results since some items containing the search term were not categorized correctly. So I added an or clause like so

$names = preg_split('/\W\s+/', $name[0]);

            if (!$names) $names = $name;

            foreach ($names as $value)
            
                $this->builder = $this->builder->orWhere('name', 'like', "%$value%");

The challenge now appears when combining this with other filters using the regular and clause. It grabs those (which I do not want) and adds the or when the condition for that fails, thus polluting the final results with unwanted rows.

I'm currently handling this by checking the presence of the or filters and avoiding them when mixed with other filters, which leaves me with a smart filter and a dumb one that searches only the category list when combined with other filters.

I understand this is more of a SQL than an eloquent problem and would like to know if there's a way to run both queries side by side i.e. filter and result set after running the ors queries

guybrush_threepwood

6 years ago

Level 33

Maybe I'm not understanding correctly, but couldn't you wrap all the orWhere queries within a callback?

$names = preg_split('/\W\s+/', $name[0]);

if (!$names) {
    $names = $name;
}

$this->builder = $this->builder->where(function($query) use ($names) {
    foreach ($names as $value) {
	$query->orWhere('name', 'like', "%$value%");
    }
});

nmeri17

6 years ago

Level 1

Um, not sure you understand. I have a list of possible filters and their query modifications. When a request comes in, I run the queries related to filters present. See

<?php

class ProductSearch {

    public $builder;

    private $smartBuild;

    function __construct( Builder $builder) {
        
        $this->builder = $builder;
    }

    public function applyFilterToQuery(array $filters) {

        $pollutants = ['subcategory', 'subcategory2', 'category'];

        $this->smartBuild = empty(array_diff( array_keys($filters), $pollutants)); // [ui=>9, mm=>4], [mm]

        foreach ($filters as $filterName => $value) {
            // dd($filters, $filterName );

            if (method_exists($this, $filterName) && !empty($value) )

                $this->$filterName( $value);
        }
        return $this;
    }

    public function location( $value) {
        
        $this->builder = $this->builder

        ->whereHas('store2', function($store) use ($value) {
            $store->where('state', $value);
        });
    }

    public function subcategory( $value) {

        $name = Subcategories::where('id', $value)->pluck('name');

        $this->builder = $this->builder->where('subcat_id', $value);
        
        if ($name->isNotEmpty() && $this->smartBuild) {

            $names = preg_split('/\W\s+/', $name[0]);

            if (!$names) $names = $name;

            foreach ($names as $value)
            
                $this->builder = $this->builder->orWhere('name', 'like', "%$value%");
        }
    }
}

You may observe from the above that making a request for categories searches products matching the category name. However, on attempting to combine that alternate match with legitimate AND queries (in location for instance, the result tends to include matching locations OR matching names. The desired result is ((matching name OR matching category) AND matching location)

I also considered fetching the queries (ANDs and ORs) separately and filtering non unique values off but I'm paginating query results so it's probably gonna turn out messy

Please or to participate in this conversation.