Clever Geek Handbook

How do I build a conditional query in Laravel?

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 to work in Laravel?

+15


source to share


9 replies


In Fluent, you can:



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

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

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

$result = $query->get();
+39


source


This is how you can fulfill your request:

$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') );


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

+10


source


Since Laravel 5.2.27 , you can avoid breaking the chain by writing your conditions like this:

$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 understand that if both conditions fail, you will return everything to the table, unless you check some other condition before querying the db / model or from the query itself.

Note that $published

both $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: Conditional Adding Instructions to Laravel Query Builder.

+6


source


If you need to use Eloquent, you can use it like, I'm not sure if whereNotNull is best to 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 all relationships still work, for example in your view you can still use

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

There is a lot of information here http://daylerees.com/codebright/eloquent-queries about this.

+4


source


You can use Model::when()

in a state or you can createBuilder::micro()

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 state. follow the instructions below.

Write the code in your server provider

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

    return $this;
});

Use as example below

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

Link: themsaid

+1


source


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

$modelQuery = Model::query();

and then chaining another query command. Maybe it will be helpful for someone new.

+1


source


for an eloquent query I used that only gets executed if the condition is

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

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

                    })
0


source


We can write like this (more precisely):

$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 the Laravel docs. Here is a pull request .

0


source


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/

0


source






More articles:


All Articles