1. Show database query in raw SQL format.
Sometimes you may want to see some database queries in raw SQL format to optimize the performance of your application or simply for debugging purposes. There are many ways to achieve this. The first option is to use query logging.
1 2 3 4 | DB::enableQueryLog(); // Bật tính năng query logging DB::table('users')->get(); // Chạy truy vấn bạn muốn ghi log Log::debug(DB::getQueryLog()); // Xuất định dạng row sql tới application log của bạn |
Another way involves using the Query Builder’s toSql function to echo raw SQL to the screen:
1 2 | echo DB::table('users')->toSql(); // prints "select * from users" |
Some functions to use with Eloquent ORM:
1 2 | echo User::get()->toSql(); // prints "select * from `users`" |
2. Making your code more readable using Eloquent query scopes.
Usually, when using Eloquent ORM in our Laravel applications, we need certain conditions when working with data. For example, consider this query:
1 2 | $active_administrators = User::where('active', '=', 1)->where('is_admin', '=', 1)->get(); |
We can easily use these conditions in a variety of locations throughout our application. To make our code more readable and not repetitive, we can use query scopes. In this example, we will create the following functions in the User model.
1 2 3 4 5 6 7 8 9 10 | public function scopeActive($query) { return $query->where('active', '=', 1); } public function scopeAdmin($query) { return $query->where('is_admin', '=', 1); } |
Now our original query should look like this:
1 2 | $active_administrators = User::active()->admin()->get(); |
3. Set model column value automatically on create
There are some situations where you want to automatically put a certain column when creating a new record. To achieve this, you can use model’s creating event inside the model’s boot method. In this example, we set the value of the paid column to 0 (false) on the Invoice model for each record we create:
1 2 3 4 5 6 7 8 9 10 11 | class Invoice extends Model { protected static function boot() { parent::boot(); Invoice::creating(function($model) { $model->paid = 0; }); } } |
4. Deleting related records automatically
During the design of an application database, it is typical to create related tables. When deleting parent records, it is also standard practice and a good database design rule to delete child-related records. To achieve this in Laravel, we can use the model’s deleting event inside the model’s boot method. For example, to delete all items of an invoice:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | class Invoice extends Model { public function items() { return $this->hasMany('AppModelsItem'); } public static function boot() { parent::boot(); static::deleting(function($invoice) { foreach ($invoice->items as $item) { $item->delete(); } }); } } |
5. Avoid errors by using the optional helper
Normally, when accessing object values, if that object is null, your code will generate an error. For example:
1 2 | return $invoice->total; |
An error will arise if the invoice object is empty. A simple way to avoid the error is to use the Laravel helper:
1 2 | return optional($invoice)->total; |
Now, if the $ invoice object is null, your code will return null instead of generating an error. You can also use closures with the helper option. It gets a closure because it’s the second argument, and it’s called if the first argument isn’t null.
1 2 3 4 | return optional(Invoice::find($id), function ($invoice) { return $invoice->total; }); |
6. Update or create
While working with databases, it is common to check if a certain record exists and update it accordingly, or create a new one. Type like this:
1 2 3 4 5 6 7 8 9 | $invoice = Invoice::where('active', 1) ->where('client', 'My client') ->where('price', 49) ->first(); if ($invoice) { $invoice->update(['price' => 29]); } |
Instead, you can use Eloquent’s updateOrCreate method:
1 2 3 4 5 | $invoice = Invoice::updateOrCreate( ['active' => '1', 'client' => 'My client'], ['price' => 29] ); |
7. Hide columns in a query result
When querying data with Eloquent, sometimes you may want to hide certain columns. There are several ways to achieve this but the quickest one is to use the makeHidden function:
1 2 | $tickets = Ticket::all()->makeHidden(['created_at', 'updated_at']); |
Source: https://laravelbit.com