Tag: Eloquent

Laravel 4 profiler – Log and display SQL queries

admin

Laravel 3 had a cool profiler bundle called Anbu, written by Daylee Rees. It was written as a bundle but Taylor, creator of Laravel, liked it so it was eventually included in the Laravel core. Laravel 4, at the time of this writing, however has no such profiler included in the core. So how do we display database SQL queries for debugging?

Here are a couple of solutions:

1. Use Loic Sharma’s Profiler. It is a PHP 5.3 profiler based off of Laravel 3’s Anbu. I have not tried this yet so just follow the instructions there.

2. Try Profiler by Juy, a PHP 5.3 profiler for Laravel 4. Backend based on sorora/omni, fronted based on loic-sharma/profiler, some features inspirated from papajoker/profiler, some features written by Juy.

3. Create an event that will listen to illuminate.query. Open your routes.php file and add this at the bottom:

 Event::listen('illuminate.query', function($sql)
 {
     var_dump($sql);
 });

This will print every query on the screen on the place where it is called so it might not look pretty,especially in loops. Also you might not see the queries on the screen in some cases since they will be “hidden” in the HTML source code (so just view the page source to see them, they will usually be at the top of the page)

4. You can also display the last executed query by placing a code in front of the code that executes a query. For example let say we have this code in our controller:

// Grab all the users
$customers = Customer::orderBy('name','asc')->get();

Place the Event::listen code in front of that statement and you will see that query displayed on your screen.

// dump the next executed query and die (dd)
Event::listen('illuminate.query', function($sql)
{
    dd($sql);
}); 

// Grab all the users
$customers = Customer::orderBy('name','asc')->get();

Keep in mind that this will only display 1 query. If your statement calls more queries they will not get displayed.

5. Put the following code in the App::before filter to get a dump of your database queries:

DB::listen(function($sql, $bindings, $time)
{
    var_dump($sql);
    var_dump($bindings);
});

6. There is another “trick” that I use sometimes (because having SQL queries displayed all the time is not practical and it messes up the code and design): I intentionally make an error in my SQL statement. For example here I mistyped type as tyspe:

$contracts = Contract::where('tyspe','=',0)->orderBy('note','asc')->get();

Since tyspe doesn’t exist Laravel returns an error,showing me the complete SQL statement:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘tyspe’ in ‘where clause’ (SQL: select * from `contracts` where `tyspe` = ? order by `note` asc) (Bindings: array ( 0 => 0, ))

Let me know if you know of any other solutions in the comments.

Laravel 4 Accessors & Mutators

admin

Eloquent provides a convenient way to transform your model attributes when getting or setting them. In Laravel 3 they were called Getter & Setter Methods. In Laravel 4 they are called Accessors & Mutators.

Accessors are custom methods in your Model that modify the value of a field before it is returned to you (e.g. before you display it on screen). Similarly, mutators are custom methods in your Model that modify the value of a field prior to being set (e.g. before being saved to database ). These methods are called automatically when you access or set the field as a property on the model (e.g. $movie->release_date).

Note: Laravel 4 adheres to the PSR-0 standard so the accessors / mutators’ method names have to be CamelCase. So, if you have an attribute called “release_date”, the appropriate mutator has to be called setReleaseDateAttribute() and the appropriate accessor has to be called getReleaseDateAttribute(). But enough of theory, let’s see an example.

Modifying date attribute with Accessors & Mutators

Let’s suppose we have a table “Movies”. The table has various fields like title,length, studio etc and of course release_date field (date). What we want to do is automatically modify/format release_date field before inserting it into database and before displaying it in our form. Now this is just for example but let’s suppose that in the database it is stored in Y-m-d format (2013-06-16) and in our form it has d/m/Y format (16/06/2013). I am using Carbon class for date conversion (you can find it in vendor/nesbot/carbon folder – it is included with Laravel 4) but you can use anything you like – even PHP date().

So open your Movies.php model (that extends Eloquent) and add this method somewhere:

//mutator for changing date d/m/Y format to Y-m-d format so it can be stored in database
 public function setReleaseDateAttribute($value)
    {
        $this->attributes['release_date'] = Carbon::createFromFormat('d/m/Y', $value)->toDateString();
    }

This might look complicated at first but don’t worry, it isn’t. As you can see we have a mutator method (setReleaseDateAttribute). If you are wondering why does this method have such name then please re-read the third paragraph of this post (about PSR-0 standard). Note that the value of the attribute is passed to mutator.

Let’s explain this line:

 $this->attributes['release_date'] = Carbon::createFromFormat('d/m/Y', $value)->toDateString();

We access the attribute “release_date” with $this->attributes['release_date'] and set/change it to whatever we want. In the right part we are using Carbon class to create ‘d/m/Y’ variable with $value and then convert it to Y-m-d format with Carbon’s toDateString() method. Again, read Carbon documentation. The right part can be anything you want :

 $this->attributes['release_date'] = '2013-06-13';

Of course that would manually set the date and ignore the actual $variable so this is just for demonstration.

Accessor works the same way, except that we use “get” instead of “set” in the method name. Also since it is just accessor we do not modify the $this->attributes['release_date'] – we just return a (new) value. So add this code below mutator code in your model:

//accessor for changing date Y-m-d format to d/m/Y format so it can be displayed in form field
 public function getReleaseDateAttribute($value). 
    {
       return Carbon::createFromFormat('Y-m-d', $value)->format('d/m/Y');
    }

As you can see we return the value with return and in the mutator we don’t – in mutator we set/modify the $this->attributes['release_date'] .(Also Carbon statement is a little different)

How does Laravel automatically know when to use accessor and when mutator?

If we access the variable somewhere in our controller or model (like $var = $movie->release_date;) then Laravel will call accessor before it is returned to us. If we set the variable (like $movie->release_date = "16/06/2013";) and then save it to database ( e.g. $movie->save() ) Laravel will call mutator before it is saved to database.

Note: If you want to use Carbon class that is included with Laravel 4 you can autoload it or simply add use Carbon\Carbon; right at the top of your model so it looks something like this:

<?php

use Carbon\Carbon;

class Movies extends Eloquent {
//your code, accessors and mutators here
}

Populate dropdown list from database with Laravel

admin

While building admin/CMS or just a form on your site you might need to build a dropdown (select) list with values read from database. It is very elegant to do this with Laravel’s Eloquent. (For the sake of this post let’s assume that you want to read values from the database and not from some array stored in a class or somewhere else.)

Let’s suppose the database table is called “countries” and is already populated (You can get one here). It contains, you guessed it, a list of country names, which you would like to display in a HTML dropbox list.

First, create a model named “Country.php” (you can use artisan, excellent  Laravel 4 generators or create it manually. Keep in mind that the model name is singular (Country) and the database table is plural (Countries).

In your controller add:

$country_options = Country::lists('short_name', 'id');

Simple isn’t it? This will return an array of values like “[1] => Afghanistan [2] => Aland Islands”… (We are assuming that database field name containing country names is “short_name”).

Note: Personally I like Eloquent but in case you do not want to use Eloquent or/and do not want to have an empty Country.php model lying there just so you can populate drop-down then you can use Laravel’s Fluent Database Query Builder:

$country_options = DB::table('countries')->orderBy('short_name', 'asc')->lists('short_name','id');

Ok, now let’s send this to view with ->with :

return View::make('admin/customers/create')->with('country_options',$country_options);

In above case I am sending $country_options to admin view that is used to create customers.

In the view you display it like this:

 {{ Form::select('country', $country_options , Input::old('country')) }}

That is all it takes to have a functioning drop-down select box. As you can see we are using Laravel Blade template engine and Form class. With Input::old('country')) we re-populate it with old values (for example after the form is submitted and there are some errors)

How to combine more fields with concat

In another case (for “customers”) I needed to combine two table fields into one and then populate the drop down with it: customers “first_name” + “last_name” should be “first_name last_name”. You can do this by combining Fluent Query Builder with Eloquent:

$customer_options = Customer::select(DB::raw('concat (first_name," ",last_name) as full_name,id'))->lists('full_name', 'id');

As you can see we are mixing Eloquent and DB:raw method.

Note: If you only want to use Laravel’s Database Query Builder then you can do this:

$customer_options = DB::table('customers')->select(DB::raw('concat (first_name," ",last_name) as full_name,id'))->lists('full_name', 'id');

How to add default option to dropdown list

Sometimes we want to have some default value that gives us some instructions what to do (“Please select a country,…”) with the dropdown or maybe even some default “non-value” (in case user doesn’t want to select anything). The easiest way to do this is to add array('' => 'Select One') + so our code looks like this (few examples with the code in this post):

$country_options = array('' => 'Please Select Your Country') + Country::lists('short_name', 'id');
//or
$customer_options = array('' => 'Select Customer') + Customer::select(DB::raw('concat (first_name," ",last_name) as full_name,id'))->lists('full_name', 'id');
//and so on..

Keep in mind that you will have to add some validation (jQuery, Laravel’s own validation etc) in case user leaves the default option (in example above we leave the value as empty but you can set it to anything you want).

I really love Laravel 4, it makes developing so much fun and simple.