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?
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();
As of Laravel 5.2.27, you can avoid breaking the chain by writing your conditions as so:
PHP < 7.4
$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();
PHP >= 7.4 (more concise)
$query = DB::table('node')
->when($published, fn ($q) => $q->where('published', 1))
->when($year, fn ($q) => $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.
when. To me a simple if statement looks way cleaner, easier to read and probably faster to execute.
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.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
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.
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
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/
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.
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();
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();
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);
})
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.