How can I build a condition based query in Laravel?

Question

I can do this in Code Igniter:

$this->db->select();
$this->from->('node');
if ($published == true)
{
    $this->db->where('published', 'true');
}
if (isset($year))
{
    $this->db->where('year >', $year);
}
$this->db->get();

How can this code be translated so that it works in Laravel?

score 56 · Accepted Answer · answered Jan 06 '13 at 06:11

56

In Fluent you can do:

$query = DB::table('node');

if ($published == true)
    $query->where('published', '=', 1);

if (isset($year))
    $query->where('year', '>', $year);

$result = $query->get();

answered Jan 06 '13 at 06:11

BenG

1,756
15
17

4

This only returns an array. is there a way for this to return a collection? – Oli Folkerd Oct 28 '14 at 14:45
this works on paginated date too, just swap get for paginate or simplePaginate – S.. Jun 23 '15 at 12:17
We can write like this (More precise way): $query = DB::table('node')->when($published, function ($q, $published) { return $q->where('published', 1); })->when($year, function($q, $year) { return $q->where('year', '>', $year); })->get() – Adiyya Tadikamalla Jun 10 '19 at 05:32
now this is not the proper way. you can use when() method. check documentation https://laravel.com/docs/8.x/queries#conditional-clauses – Syamlal Dec 31 '21 at 12:10
1

I wouldn't say this isn't the 'proper' way @syam lal. There is a different way based on the docs above, but I personally find this easier to read, so would actually consider *this* to be the proper way. But, to each his own... – Watercayman Apr 17 '22 at 19:35

score 22 · Answer 2 · answered Aug 19 '16 at 18:51

As of Laravel 5.2.27, you can avoid breaking the chain by writing your conditions as so:

$query = DB::table('node')
    ->when($published, function ($q) use ($published) {
        return $q->where('published', 1);
    })
    ->when($year, function($q) use ($year) {
        return $q->where('year', '>', $year);
    })
    ->get();

To use Eloquent,just swap $query = DB::table('node') with Node:: but realize if both conditions fail, you'll get everything in the table back unless you check for some other condition before querying the db/model or from within the query itself.

Note the that $published and $year must be in local scope to be used by the closure.

You can make it more concise and readable by creating a macro. See: Conditionally adding instructions to Laravel's query builder

I don't really see the advantages of `when`. To me a simple if statement looks way cleaner, easier to read and probably faster to execute. — Arno van Oordt, Dec 10 '20 at 10:16
@ArnovanOordt What notication looks cleaner is subjective, as I find the `when` notation more clean because it gives one context of the intention making it easier to read for me. That being said, I would like to see some data backing up your intuition that it is faster to execute. If I had to guess the differences are not significant or maybe not even measurable. — Robin Bastiaan, Aug 09 '22 at 12:12

Joel Larson · Answer 3 · 2013-01-06T06:17:54.077

Here is how you can accomplish your query:

$year = 2012;
$published = true;

DB::table('node')
->where(function($query) use ($published, $year)
{
    if ($published) {
        $query->where('published', 'true');
    }

    if (!empty($year) && is_numeric($year)) {
        $query->where('year', '>', $year);
    }
})
->get( array('column1','column2') );

To find more information, I recommend reading through Fluent and Eloquent in the Laravel docs. http://laravel.com/docs/database/fluent

score 11 · Answer 4 · answered Mar 15 '19 at 19:51

11

I have not seen it here. You can even start your query like

$modelQuery = Model::query();

and then chain other query command afterwards. Maybe it will be helpful for someone new.

answered Mar 15 '19 at 19:51

Bartando

719
8
26

score 7 · Answer 5 · answered Apr 29 '17 at 05:16

You can use Model::when() in Condition or you can create Builder::micro()

For Example

$results = Model::where('user_id', Auth::id())
    ->when($request->customer_id, function($query) use ($request){
        return $query->where('customer_id', $request->customer_id);
    })
    ->get();

If You need to create micro for a condition then. follow below instruction.

Write thic code in your serverice provider

Builder::macro('if', function ($condition, $column, $operator, $value) {
    if ($condition) {
        return $this->where($column, $operator, $value);
    }

    return $this;
});

Use Like Below Example

$results = Model::where('user_id', Auth::id())
    ->if($request->customer_id, 'customer_id', '=', $request->customer_id)
    ->get();

Ref: themsaid

score 5 · Answer 6 · answered Mar 08 '15 at 20:17

If you need to use Eloquent you can use it like, I'm not sure that whereNotNull is the best use but I couldn't find another method to return what we really want to be an empty query instance:

$query = Model::whereNotNull('someColumn');
if(x < y)   
    {
        $query->where('column1', 'LIKE', '%'. $a .'%');
    }else{
        $query->where('column2', 'LIKE', '%'. $b .'%');
    }
$results = $query->get();

This way any relationships still work, for example in your view you can still use

foreach($results as $result){
    echo $result->someRelationship()->someValue;
}

There is a good amount of info on here http://daylerees.com/codebright/eloquent-queries about this sort of stuff.

whereRaw(1) might be a better alternative to whereNotNull(fieldname) — Ahmed Shefeer, May 31 '18 at 10:25

score 5 · Answer 7 · edited Jul 02 '19 at 04:27

5

In Laravel > 5.2 you can use when():

$results = DB::table('orders')
    ->where('branch_id', Auth::user()->branch_id)
    ->when($request->customer_id, function($query) use ($request){
        return $query->where('customer_id', $request->customer_id);
    })
    ->get();

Docs: https://laravel.com/api/5.8/Illuminate/Contracts/Container/Container.html#method_when

Blog post: https://themsaid.com/laravel-query-conditions-20160425/

edited Jul 02 '19 at 04:27

Karl Hill

12,937
5
58
95

answered Jun 19 '19 at 16:44

Connor Leech

18,052
30
105
150

1

I used this in Dec 2020 and it did exactly what I needed. Thank you! – James Stewart Dec 04 '20 at 14:49

score 1 · Answer 8 · answered May 25 '23 at 05:48

1

use App\Models\User;

$query = User::query();

if ($request->has('name')) {
    $query->where('name', 'like', '%' . $request->input('name') . '%');
}

if ($request->has('age')) {
    $query->where('age', '>=', $request->input('age'));
}

// Execute query
$users = $query->get();

answered May 25 '23 at 05:48

Sejal Varu

22
6

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 25 '23 at 07:37
This way is better then using ->when because ->when only accepts boolean condition. – Steferson Jun 16 '23 at 21:09

score 0 · Answer 9 · answered Mar 30 '17 at 12:50

for eloquent query i used following that executes only if where condition has value

->where(function($query) use ($value_id)
                    {

                            if ( ! is_null($value_id)) 
                                $query->where('vehicle_details.transport_type_id', $value_id);

                    })

score 0 · Answer 10 · answered Jun 10 '19 at 05:38

We can write like this (More precise way):

$query = DB::table('node')->when($published, function ($q, $published) {
        return $q->where('published', 1);
    })->when($year, function($q, $year) {
        return $q->where('year', '>', $year);
    })->get()

Not mentioned in Laravel docs. Here is pull request.

score 0 · Answer 11 · answered May 29 '23 at 10:08

Building a condition-based query using the query builder:

$users = DB::table('users')
            ->where('active', true)
            ->orWhere('age', '>', 18)
            ->get();

If you're using Eloquent ORM, you can achieve the same result using the Eloquent query builder:

$users = User::where('active', true)
             ->orWhere('age', '>', 18)
             ->get();

How can I build a condition based query in Laravel?

11 Answers11

Linked