Filter query not working through Laravel query builder

Question

I have a filter query where it checks for three parameters id, keywords and status. It's working totally fine when I run this query in MySQL but when I try to run it through Laravel's query builder it's fetching all the results of that id.

Here is the query which I'm running in MySQL(and it's working fine like I mentioned):

SELECT * FROM `poems`
WHERE book_id = 2
AND p_english_keywords LIKE '%freedom%'
AND p_status = 1

And here is the Laravel's query builder code:

$poems = Poems
     ::where('book_id', '=', $filter, 'AND', 'p_english_keywords', 'LIKE', '%'.$query.'%', 'AND', 'p_status', '=', 1)
     ->orWhere('book_id', '=', $filter, 'AND', 'p_spanish_keywords', 'LIKE', '%'.$query.'%', 'AND', 'p_status', '=', 1)
     ->orWhere('book_id', '=', $filter, 'AND', 'p_german_keywords', 'LIKE', '%'.$query.'%', 'AND', 'p_status', '=', 1)
     ->orWhere('book_id', '=', $filter, 'AND', 'p_urdu_keywords', 'LIKE', '%'.$query.'%', 'AND', 'p_status', '=', 1)
     ->paginate(10);

This query looks totally fine and it's also working fine if a user removes the filter. It's searching for the keywords in user's language. But whenever a user applies a filter, it shows all the results from the filtered book instead of specific keywords in that book. Please Help!

[`orWhere`](https://laravel.com/api/5.8/Illuminate/Database/Eloquent/Builder.html#method_orWhere) only takes up to 3 arguments? You should use a closure instead — Brian Thompson, Nov 20 '19 at 20:41
Yeah, the signature for `where()` is `where($column, $operator = null, $value = null, $boolean = 'and')`, so it's ignoring everything after `'AND'`. If you run `->toSql()` instead of `->paginate()`, you can see the generated SQL, and debug it a little better. `where()` also accepts an array as it's first param, but you'd have to pass `where([], null, null, 'AND')` to get it to work correctly. — Tim Lewis, Nov 20 '19 at 20:45
@BrianThompson I don't know whether it takes three params or more. I'm actually new to Laravel and still learning. I've read the [documentation](https://laravel.com/docs/6.x/queries#where-clauses) and it said you can use 3 params for a basic `where()` clause, reading which I assumed, it could take more and It did somehow. Means this query should work and is working totally fine `Poems::where( 'p_english_keywords', 'LIKE', '%'.$query.'%', 'AND', 'p_status', '=', 1)`. I don't know why, but its working. — Saud, Nov 24 '19 at 12:41
@TimLewis, I checked the query with `toSql()` and it helped to debug and Yeah, I know about the arrays as a parameter. I've ran a few queries through array parameters like, `TableName::where(['column' => $value, 'column2' => $value2]);` and it worked for me. I hope I understood what you said. — Saud, Nov 24 '19 at 13:24

score 2 · Answer 1 · answered Nov 20 '19 at 20:40

I'm not sure why Poems::where('book_id', '=', $filter, 'AND', 'p_english_keywords', 'LIKE', '%'.$query.'%', 'AND', 'p_status', '=', 1) ever worked for you since that syntax is not in the documentation anywhere so I'm gonna rewrite your query.

$poems = Poems::where(function ($q) use ($filter, $query) {
    $q->where([
        ['book_id', '=', $filter],
        ['p_english_keywords', 'like', '%'.$query.'%'],
        ['p_status', '=', 1],
    ]);
})
->orWhere(function ($q) use ($filter, $query) {
    $q->where([
        ['book_id', '=', $filter],
        ['p_spanish_keywords', 'like', '%'.$query.'%'],
        ['p_status', '=', 1],
    ]);
})
->orWhere(function ($q) use ($filter, $query) {
    $q->where([
        ['book_id', '=', $filter],
        ['p_german_keywords', 'like', '%'.$query.'%'],
        ['p_status', '=', 1],
    ]);
})
->orWhere(function ($q) use ($filter, $query) {
    $q->where([
        ['book_id', '=', $filter],
        ['p_urdu_keywords', 'like', '%'.$query.'%'],
        ['p_status', '=', 1],
    ]);
})
->paginate(10);

However, since you always make the the same filters (book_id = $filter and p_status = 1), I think this can be reduced even further:

$poems = Poems::where([
    ['book_id', '=', $filter],
    ['p_status', '=', 1],
])
->where(function ($q) use ($query) {
    $q->where('p_english_keywords', 'like', '%'.$query.'%')
    ->orWhere('p_spanish_keywords', 'like', '%'.$query.'%')
    ->orWhere('p_german_keywords', 'like', '%'.$query.'%')
    ->orWhere('p_urdu_keywords', 'like', '%'.$query.'%');
})
->paginate(10);

Using toSql(), I get the following query, which I think you're looking for:

select * from `poems`
where (
    `book_id` = ? and `p_status` = ?
)
and (
    `p_english_keywords` like ?
    or `p_spanish_keywords` like ?
    or `p_german_keywords` like ?
    or `p_urdu_keywords` like ?
)

Filter query not working through Laravel query builder

1 Answers1