Please note that this chapter was written for VERSION 3 of the Laravel PHP Framework.
Fluent is another wonderful library that Laravel provides to help me dodge the SQL bullet, although you can still write raw SQL statements if you happen to enjoy pain. The best part about using Fluent, apart from the lack of SQL, is that it uses prepared statements which are fully protected against SQL injection. Fluent is also... well fluent in a number of different SQL dialects, so your methods will work across a variety of databases. Before we get started you will need to understand the concept of method chaining, take a look at this example.
<?php
Class::make()->chain()->chain()->chain()->trigger();
This is a very useful way of stringing options together, and is a lovely way of expressing SQL (as you will see later). The class is instantiated with the make() method. Sometimes you will also pass 'startup parameters' to the class this way. The chain() methods are used to modify the request with different options, and the trigger() method is used to bring back the final result. This might sound a bit confusing, but let's take a look at an example from fluent.
<?php
$users = DB::table('users')->where('username', '=', 'dayle')->get();
The above example will execute a simple..
SELECT * FROM users WHERE username = 'dayle';
and bring back an array of objects, representing database rows that are the result of the query.
The table() method is instantiating the object, and setting the table that we intend to work with. The where() is a chain method that applies a WHERE clause to our query, and get() is the final trigger method that retrieves all objects that are the result of the query.
The get() trigger will bring back an array of results, so lets start by looping the result set with a foreach.
<?php
foreach ($users as $user)
{
echo $user->email;
}
As you can see in the above example, fields for the row are accessed using the attributes of the result object. The loop above would output the email addresses for all of our users.
Retrieving Results
Let's have a look at some of the other trigger methods for retrieving results.
get()
We have just used this, it will bring back all objects which are the result of the query in an array.
first()
This trigger will bring back a single result object. The first object to match the requirements of the query.
find($id)
This trigger will find the item by its database id. This is a handy shortcut to where('id', '=', $id). It will bring back a single result object.
only($fieldname)
This will bring back the result for a single field matching the query.
get(array())
Pass an array of fields to get() to retrieve only those fields.
Where Clauses
So now we have the methods that we need to retrieve our database results, how can we apply conditions to these SQL queries? Well in the example above you saw me using the where() method, let's take a closer look at this.
<?php
$users = DB::table('users')->where('username', '=', 'dayle')->get();
Here we have the same snippet again, but we are concentrating on the where() part of the chain :
<?php
where('username', '=', 'dayle')
The great thing about how Laravel handles the where clause is that the chain looks a little bit like the SQL being generated. In the chain method above we are saying WHERE username = 'dayle'. The first parameter to the method states the field which we are comparing, the second parameter states the operator to use for the comparison, and the third parameter is the value which we are comparing with. We could also use :
<?php
where('age', '>', '18')
// WHERE age > '18'
// Drink up! Not if you are American though, sorry.
What if we want more conditions? Well first we need to decide if we need 'AND WHERE' or 'OR WHERE'. To include an 'AND' where clause, simply use the where() method again in the chain, for example :
<?php
$users = DB::table('users')
->where('username', '=', 'dayle')
->where('sexyness', '>', 5000)
->get();
As you can see in the above example, I have put each chain method on a new line. I find this is easier to read, and avoids having terribly long lines of code. This chain will perform the following SQL :
SELECT * FROM users WHERE username = 'dayle' AND sexyness > 5000;
If we would prefer to use an OR where condition we simply use the or_where() method, which accepts the same parameters. For example :
<?php
$users = DB::table('users')
->where('username', '=', 'dayle')
->or_where('face', 'LIKE', '%malemodel%')
->get();
which gives us :
SELECT * FROM users WHERE username = 'dayle' OR face LIKE '%malemodel%';
Now I don't need to explain how every feature of SQL works, there are plenty of other books for that. I will however name the methods used to accomplish common tasks instead.
Use the where_in(), where_not_in(), or_where_in() and or_where_not_in() methods to match a field to one of an array of items.
The where_null(), where_not_null(), or_where_null(), and or_where_not_null() methods to match a field to a NULL value.
Sometimes you will want to nest where clauses together, Laravel provides functionality for this in the form of 'Nested Where Clauses'. Let's take a look at a code example..
<?php
$users = DB::table('users')
->where('id', '=', 1)
->or_where(function($query)
{
$query->where('age', '>', 25);
$query->where('votes' '>', 100);
})
->get();
By passing a closure containing extra where clauses to another where method, we can create nested where clauses. The result can be seen in the SQL:
SELECT * FROM "users" WHERE "id" = ? OR ("age" > ? AND "votes" > ?)
neat huh?
And now for a more interesting feature! (I told you about how I loathe SQL right?) Dynamic where clauses give you a really funky way of defining simple where clauses. Check this out..
<?php
where_size(5)->get();
Here we are specifying the field to compare in the method name. Fluent is so clever that it will deal with it, no problem!
It will even understand AND and OR, check this out!
<?php
where_size_and_height(700, 400)->get();
Expressive, clean, Laravel at its finest.
Table Joins
Let's have a look at a join using Fluent.
<?php
DB::table('tasks')
->join('project', 'tasks.id', '=', 'project.task_id')
->get(array('task.name', 'project.name'));
We pass the name of the join table as the first parameter, and use the remaining three parameters to perform an 'ON' clause, similar to how we perform 'WHERE's.
We then pass the fields we want to return to the get() method as an array.
We can do a left_join() exactly the same way, in fact it takes the same parameters, easy huh?
Do you remember the nested where clauses? Well you can use a similar technique to add more conditions to the ON clause of a join. Let's take a look.
<?php
DB::table('tasks')
->join('project', function($join) {
$join->on('tasks.id', '=', 'project.task_id');
$join->or_on('tasks.author_id', '=', 'project.author_id');
})
->get(array('task.name', 'project.name'));
In this case, we pass a closure as the second parameter to the join() method, then use the on(), or_on() and and_on() methods to set the conditions.
Ordering
Ordering is pretty important, you don't wanna waste resources doing that with PHP. You could... sure... lots of nasty array_sort()ing, tons of loops, that wouldn't be any fun. Let's pass this responsibility to fluent.
<?php
DB::table('shoes')->order_by('size', 'asc')->get();
Hmm shoes? Women are meant to think about shoes but thats all that came to mind.. scarey. Anyway its as easy as passing a field name and either asc for ascending, or desc for descending. To sort on more columns simply repeat the order_by() chain.
Limiting.. no Taking
What if we only want to get a certain number of results back? In SQL we would use LIMIT bah that sounds stupid, Laravel provides take().
<?php
DB::table('shoes')->take(10)->get();
Now I want 10 shoes? I should be more worried.. But it's clear, limiting is very simple!
Skipping Results
We don't need the first 5 pairs of shoes do we? They are leather shoes and I like to wear skate shoes. Very wide feet you see.. Let's just skip them.
<?php
DB::table('shoes')->skip(5)->get();
There we go, now we can skip() the first 5 results. Easy as pie!
Aggregates
Sometimes its handy to run basic maths on queries. AVG, MIN, MAX, SUM, and COUNT are used all the time to quickly get the result we want with SQL, and they are all available with the Fluent Query Builder. Let's take a look.
<?php
$val = DB::table('shoes')->avg('size');
$val = DB::table('shoes')->min('size');
$val = DB::table('shoes')->max('size');
$val = DB::table('shoes')->sum('size');
$val = DB::table('shoes')->count();
Simple! Don't forget that these are trigger methods. We don't need to add get() here. You are also welcome to add conditions with where() or whatever you like!
Expressions
The methods we have been using so far escape and quote the parameters you provide automatically, but what if we need something really custom? What if we don't want anything else added? For this we can use the DB::raw() method. Here's an example :
<?php
DB::table('shoes')->update(array('worn' => DB::raw('NOW()')));
In this query the NOW() will not be escaped or quoted. Lots of freedom there, but don't forget that spiderman quote! Be responsible with this power.
++ (or decrementing)
What if we want to simply increment or decrement a value? Easy as pie!
<?php
DB::table('shoes')->increment('size');
DB::table('shoes')->decrement('size');
Simply pass a field name, and voila!
Insert
Finally, let's store some data. All this time we have been looking at reading data but this will be more fun! Well it's quite simple really. To insert a new row all we have to do is provide a key-value array to the insert() method, which is a trigger method by the way!
<?php
DB::table('shoes')->insert(array(
'color' => 'hot pink',
'type' => 'heels',
'size' => '12'
));
Wait, lets grab the id thats created with this new row, it might be handy later? We can use insert_get_id() with the same parameters for this.
<?php
$id = DB::table('shoes')->insert_get_id(array(
'color' => 'hot pink',
'type' => 'heels',
'size' => '12'
));
That's my weekend pair, let's keep this between us. We now have a nice hot pink pair of heels in size 12, stored in the shoes table of our database.
Update
Wait, did I say heels in that last section? They were hot pink skate shoes! If only we could go back and fix our mistake. I could update the table and no one would see my shame. Oh I guess we could probably use the update() method, you see it takes an array with the same syntax as insert().
<?php
DB::table('shoes')->update(array(
'type' => 'skate shoes'
));
Hold on, we aren't specifying a record here. I don't want to change all my records to skate shoes, it would ruin my wonderful Laravel flip flops. Let's use a where() method and that $id we got earlier to narrow it down to the record we want. Chain time!
<?php
DB::table('shoes')
->where('id', '=', $id)
->update(array(
'type' => 'skate shoes'
));
There, we fixed the problem before anyone noticed.
Delete
To delete a row (please, not the shoes, they are innocent!) we can use the delete() method with a where() clause, or simply pass it an id directly to delete a single row. Let's see these methods in action.
<?php
DB::table('not_shoes')->where('texture', '=', 'fuzzy')->delete();
Simple. The fuzzy things are now gone!
<?php
DB::table('shoes')->delete($id);
NO! Not the hot pink skate shoes. You have obviously learned too much, please keep the responsibility and power thing in mind. We don't want any more shoe massacres.
In the next chapter, we will be moving on from sho... Fluent, and taking a look at Eloquent. Eloquent will allow us to treat our database rows as objects, and provides an elegant.. or eloquent solution to handling relationships.
My books are available online for free to encourage learning. However, if you'd like for me to keep writing, then please consider buying a digital copy over at Leanpub.com.
It's available in PDF, ePub, and Kindle format, and contains a bunch of extras that you won't find on the site. I have a full-time job, and I write my books in my spare time. Please consider buying a copy so that I can continue to write new books from the comfort of my sofa!