Append orWhere() to dynamic subquery

Question

User can submit a search form with an input such as dog cat +bird. This is expected to return all Posts with title containing (dog OR cat) AND bird.

I believe I need to append dog and cat in a subquery such as :

protected function orLike(string $column, string $value): Builder
{
    return $this->builder
        ->where(function (Builder $subQuery) use ($column, $value) {
            $subQuery->orWhere($column, 'like', '%'.$value.'%'); // dog
            //$subQuery->orWhere($column, 'like', '%'.$value.'%'); // cat
            //$subQuery->orWhere($column, 'like', '%'.$value.'%'); etc...
        });
}

above orLike is my function in a loop that runs for each parsed optional search term (dog, cat)

How do I make it so that each optional terms (dogor cat) APPEND to the $subquery with an orWhere() for each term?

Right now, obviously, it fires a new where() with a single subquery for each term.

Not sure if I am clear enough. Basically I am trying to build a rather simple search input where users can type +bird -cat dog duck meaning bird MUST be in the title AND cat MUST NOT be in the title AND (contains dog OR duck)

edit: additional info as per request in the comments

/*
usage: return $this->parseLike('title', '+dog cat -bird elephant duck');
*/

protected function parseLike(string $column, string $value)
{

    // [...] irrelevant code

    /*
    $terms is a collection of terms, such as:
    +dog
    cat
    -bird
    elephant
    duck
    */

    return $terms
        ->unique()
        ->map(function (string $term) {
            switch (\substr($term, 0, 1)) {
                case '+':
                    return ['like' => \substr($term, 1)]; // function "like()" is called for terms with operator "+" such as "+dog"
                case '-':
                    return ['notLike' => \substr($term, 1)]; // function "notLike()" is called for terms with operator "-" such as "-bird"
                default:
                    return ['orLike' => $term]; // function "orLike()" is called for terms with no operator, such as "elephant" or "duck" or "cat"
            }
        })
        ->each(function ($combination) use ($column) {
            collect($combination)
                ->reject(function ($term) {
                    return empty($term);
                })
                ->each(function (string $term, string $operator) use ($column) {
                    return $this->{$operator}($column, $term);
                });
        });
}

user_10547185 · Answer

Look at this example, I believe you can integrate it to your code.

All you need is to create arrays with values you 1.need, 2.don't need, 3.may be

    $mustBe = ['dog', 'cat'];
    $mustNotBe = ['bird'];
    $mayBe = ['tiger', 'lion'];

    $model = SomeModel::query();

    foreach ($mustBe as $term){
        $model->where('title', 'like', '%'. $term . '%');
    }
    foreach ($mustNotBe as $term){
        $model->where('title', 'not like', '%'. $term . '%');
    }

    if($mayBe){
        $model->where(function ($query) use ($mayBe) {
            foreach ($mayBe as $term){
                $query->orWhere('title', 'like', '%'. $term . '%');
            }
        });
    }

    $result = $model->get();
    dd($result);

    // this builder will return something like this
    $result = SomeModel::where('title', 'like', '%dog%') // dog must be
        ->where('title', 'like', '%cat%') // cat must be
        ->where('title', 'not like', '%bird%') // bird must not be
        ->where(function ($query) {
            $query->orWhere('title', 'like', '%tiger%') // tiger may be
                  ->orWhere('title', 'like', '%lion%'); // lion may be
        })
    ->get();

Append orWhere() to dynamic subquery

1 Answers