Laravel includes Eloquent, an object-relational mapper (ORM) that makes it pleasant to interact with your database. Eloquent ORM seems like a simple mechanism, but under the hood, there’s a lot of semi-hidden functions and less-known ways to achieve more with it. In this article, I will show you some latest tips and tricks related to Laravel Eloquent and DB Models.
JSON Where Clauses
Laravel offers helpers to query JSON columns for databases that support them.
Currently, MySQL 5.7+, PostgreSQL, SQL Server 2016, and SQLite 3.9.0 (using the JSON1 extension)
// To query a json column you can use the -> operator
$users = User::query()
->where('preferences->dining->meal', 'salad')
->get();
// You can check if a JSON array contains a set of values
$users = User::query()
->whereJsonContains('options->languages', [
'en', 'de'
])
->get();
// You can also query by the length of a JSON array
$users = User::query()
->whereJsonLength('options->languages', '>', 1)
->get();
Chain conditional clauses to the query without writing if-else statements
The "when" helper in the query builder is 🔥
You can chain conditional clauses to the query without writing if-else statements.
This makes your query very clear:
class RatingSorter extends Sorter
{
function execute(Builder $query)
{
$query
->selectRaw('AVG(product_ratings.rating) AS avg_rating')
->join('product_ratings', 'products.id', '=', 'product_ratings.product_id')
->groupBy('products.id');
->when(
$this->direction === SortDirections::Desc,
fn () => $query->orderByDesc('avg_rating')
fn () => $query->orderBy('avg_rating'),
);
return $query;
}
}
Get all the column names for a table
DB::getSchemaBuilder()->getColumnListing('users');
/*
returns [
'id',
'name',
'email',
'email_verified_at',
'password',
'remember_token',
'created_at',
'updated_at',
];
*/
Accessor Caching
As of Laravel 9.6, if you have a computationally intensive accessor, you can use the shouldCache method.
public function hash(): Attribute
{
return Attribute::make(
get: fn($value) => bcrypt(gzuncompress($value)),
)->shouldCache();
}
New scalar() method
In Laravel 9.8.0, the scalar() method was added that allows you to retrieve the first column of the first row from the query result.
// Before
DB::selectOne("SELECT COUNT(CASE WHEN food = 'burger' THEN 1 END) AS burgers FROM menu_items;")->burgers
// Now
DB::scalar("SELECT COUNT(CASE WHEN food = 'burger' THEN 1 END) FROM menu_items;")
Select specific columns
To select specific columns on a model you can use the select method -- or you can pass an array directly to the get method!
// Select specified columns from all employees
$employees = Employee::select(['name', 'title', 'email'])->get();
// Select specified columns from all employees
$employees = Employee::get(['name', 'title', 'email']);
Compare the values of two columns
You can use whereColumn method to compare the values of two columns.
return Task::whereColumn('created_at', 'updated_at')->get();
// pass a comparison operator
return Task::whereColumn('created_at', '>', 'updated_at')->get();
Eloquent has many excellent functionalities, which I explained above. I hope you will find them helpful and implement them in your Laravel Projects.
Follow Us on Twitter:
Top comments (0)