Example of Simple 'Advance Where' Query Builder in Laravel

Laravel tips this time I will give a simple code example for Searching the database using Query Builder in laravel.

For example, we want to Search in a table, say, the 'article' table. there may have been a code like this before

//MyModel.php

public static function getSearch ($ search = null) {
    
    // is_publish: 0 = pending, 1 = published

    $ result = DB :: table ('article')
        -> where ('article.is_publish', '1')
        -> where ('article.title', 'LIKE', '%'. $ search. '%')
        -> orWhere ('article.content', 'LIKE', '%'. $ search. '%')
        -> select ('*')
        -> orderBy ('article.id', 'desc') -> paginate (10);

    return $ result;
}
Query Scenario above is looking for WHERE article has been published (is_publish = 1) AND article title like '% ...%' OR article content like '% ...%'.

SELECT * FROM `article` WHERE` article`. `is_publish` = '1' AND `article '.` Title' LIKE '% search%' OR `article`.` Content` LIKE '% search%' ...

The query above is not an error but it will produce incorrect results where the article that is_publish = 0 also appears. To make it right, we must create a code like the following

public static function getSearch ($ search = null) {
    
    // is_publish: 0 = pending, 1 = published

    $ result = DB :: table ('article')
        -> where ('article.is_publish', '1')
        -> where (function ($ query) use ($ search) {

            $ query-> where ('article.title', 'LIKE', '%'. $ search. '%')
                  -> orWhere ('article.content', 'LIKE', '%'. $ search. '%');
        })
        -> select ('*')
        -> orderBy ('article.id', 'desc') -> paginate (10);

    return $ result;
}
The above code will generate the following Raw query

SELECT * FROM `article` WHERE` article`. `is_publish` = '1' AND (`article`.` Title` LIKE '% search%' OR `article` .`content` LIKE '% search%') .. .
Note that we must use Closure

....
-> where (function ($ query) use ($ search) {

    $ query-> where ('article.title', 'LIKE', '%'. $ search. '%')
           -> orWhere ('article.content', 'LIKE', '%'. $ search. '%');
 })
because we access variables outside the function.

Share this