How-to: Laravel 4 tutorial; part 5 – using databases

[easyreview title=”Complexity rating” icon=”geek” cat1title=”Level of experience required, to follow this how-to.” cat1detail=”There are some difficult concepts here, but you’ll find this is pretty easy in practice.” cat1rating=”3″ overall=”false”]

Laravel Tutorials

layered database

Introduction

At first sight, Laravel offers a dizzying range of ways to interact with your databases. We’ve already seen Migrations and the Schema Builder. There’s also the DB Class with its Query Builder and the Eloquent ORM (Object Relational Mapper) plus no doubt plenty of database plugins for various enterprise and edge-use cases. So where to start?

I’d counsel you to give Eloquent serious consideration – especially if you’ve never previously encountered an ORM. Coming from CodeIgniter which certainly didn’t use to have a built-in ORM, I was amazed how much quicker the Doctrine ORM made it to code database manipulation. And the resulting code was easier to understand and more elegant. Laravel comes with its own built-in ORM, in Eloquent. For me, tight integration with a decent ORM is one of the reasons I turned to Laravel in the first place, so it would take a lot to tempt me away from it to a third-party plug-in. But the great thing about this framework is that it gives you choice – so feel free to disagree. In any event, in this tutorial, Eloquent will be our object of study.

Models

Laravel follows the MVC (Model View Controller) paradigm. If you’re frequently the sole developer on a project, you’ll find that this forces you into almost schizophrenic modes of development. “Today I am a user interface designer, working on views. I know nothing of business logic. Don’t come here with your fancy inheritance and uber_long_function_names().” This is honestly helpful; it forces you into a discipline that results in more easily maintainable code.

Models describe (mostly, but not exclusively) how you interact with your database(s). Really they deal with any data that might be consumed by your application, whether or not it resides in a traditional database. But one step at a time. Here we’ll be looking at Eloquent with a MySQL database. Eloquent is database agnostic though (to a point), so it doesn’t really matter what the underlying engine is.

Unless you have a really good reason not to, it’s best to place your model files under app/models. In the last tutorial, I created (through a migration) a “nodes” table. I mentioned that it was significant that we use a plural noun. Now I’m going to create the corresponding model, which uses the singular form of the noun. The table name should normally be lower case, but it’s preferred to use title case for the class name. My file is app/models/Node.php. Initially, it contains:


The closing "?>" tag is not needed.

Eloquent assumes your table has a primary key called "id". This assumption can be overridden, as can the assumed table name (see the docs).

Now that teeny weeny bit of code has caused all sorts of magic to happen. Head back to the ScrapeController.php file I created in tutorial 2, and look what we can do:

	public function getNode($node) {
		// Top 10 downloads that have at been downloaded at least 50 times
		$nodes = Node::where('downloads', '>', 50)
			->take(10)
			->orderBy('downloads', 'DESC')
			->get();
		$this_node = Node::find($node);
		if($this_node) $data['this_url'] = $this_node->public_url;
		$data['nodes'] = $nodes;
		return View::make('node', $data);
	}

Coming from CodeIgniter, where you had to load each model explicitly, that blew me away. The Eloquent ORM class causes your new Node model to inherit all sorts of useful methods and properties.

  • All rows: $nodes = Node::all();
  • One row (sorted): $top = Node::orderBy('downloads', 'DESC')->first();
  • Max: $max = Node::max('downloads');
  • Unique rows: $uniq = Node::distinct('public_url')->get();
  • Between: $between = Node::whereBetween('downloads', array(20, 50))->get();
  • Joins: $joined = Node::join('mp3metadata', 'mp3metadata.ng_url', '=', 'nodes.public_url')->get();

As you'd expect there are many more methods than I would want to describe here. Just something to bear in mind when reading the official documentation: not only can you use all the methods describe in the Eloquent docs, you can also use all the methods described in the Query Builder docs.

CRUD

At the very least, we need to know how to Create, Read, Update and Delete rows. All the following examples are of logic you'd typically use in a controller.

Create

$new_node = new Node;
$new_node->public_url = 'http://some.url/';
$new_node->blurb = 'blah blah blah';
$new_node->speaker = 'Fred Bloggs';
$new_node->title = 'Great Profundities';
$new_node->date = date('Y-m-d');
$new_node->save();

Note that the created_at and updated_at fields are automatically maintained when you use save().

Read

See the examples above to see how records can be retrieved. Eloquent returns a Collection object, for multi-record results. Collections have a few special methods. I confess I am not clear on their usage, due to lack of working examples. The methods that seems most helpful is each() for iteration. The official docs give a terse example:

$roles = $user->roles->each(function($role)
{

});

Update

// Retrieve and update
$node = Node::find(1);
$node->downloads = 64;
$node->save();

// Using a WHERE clause
$changes = Node::where('downloads', '<', 100)->update(array('downloads' => 100));

Delete

// Several options
$node = Node::find(1);
$node->delete();

Node::destroy(1, 2, 3);
		
$deleted = Node::where('downloads', '<', 100)->delete();

Relationships

There's every chance that you will be working with data where items in one table have a relationship with items in another table. The following relationships are possible:

  • One-to-one
  • One-to-many
  • Many-to-many
  • Polymorphic

I'm not going to dwell too much on the meaning of these, since my objective is not to offer a relational database primer. 😉

For convenience (and because they make sense!) I'm quoting the relationships referenced in the official documentation.

One-to-one
In the User.php model:

class User extends Eloquent {

    public function phone()
    {
        return $this->hasOne('Phone');
    }

}

Eloquent assumes that the foreign key in the phones table is user_id. You could then in a controller do: $phone = User::find(1)->phone;

Relationships can be defined in either direction for convenience, so you can go from the User to the Phone or from the Phone to the user. The reverse relationship here would be defined in Phone.php model file as follows:

class Phone extends Eloquent {

    public function user()
    {
        return $this->belongsTo('User');
    }

}

One-to-many

Forwards:

class Post extends Eloquent {

    public function comments()
    {
        return $this->hasMany('Comment');
    }

}

Reverse:

class Comment extends Eloquent {

    public function post()
    {
        return $this->belongsTo('Post');
    }

}

And in your controller: $comments = Post::find(1)->comments;

Many-to-many

Many-to-many relationships break down into two one-to-many relationships, with an intermediate table. For example, each person may drive multiple cars; conversely each one car may be driven by multiple people. You would define an intermediate people_cars table and set up one-to-many relationships between this table and the two other tables.

Polymorphic

Polymorphic relationships are a little odd. You could define a relationship between multiple tables, when a query to a single model will retrieve results from more than one related table based on similar one-to-many relationships. Maybe I'm not getting it, but personally I would use different types of join to achieve similar results - and I would find that easier to understand, document and maintain. But by all means, read the docs and see if this strategy works for you.

Conclusion

As you'd expect, you can dig a lot deeper with Eloquent. There's enough here to get you started though. If you want to soak up the full benefits of Eloquent, you may wish to consult the API documentation, or read the source code. I'll leave such fun activities for people with bigger brains than mine though. 😉

Layered Database image copyright © Barry Mieny, licensed under Creative Commons. Used with permission.