This title was written for Laravel version 4. If you're looking for material on the latest version of Laravel, then please check out Code Smart.
In the previous chapter we discovered how to express our database rows as columns, and our tables as classes. This removes the need for writing statements using the Structured Query Language (SQL) and will result in code that is much more readable. We're writing PHP, right? Why bother complicating things by adding another language?
Well, there are some good bits about the SQL. For example, the Q part. Querying. With SQL, we can use a number of complex comparisons and set arithmatic to retrieve only the results that we require. Replicating all of this functionality with Eloquent would be a tremendous task, but fortunately, Eloquent has alternate methods for the most useful of queries. For all the bits that are missing we can use raw queries to provide SQL statements that will return Eloquent ORM result instances. We'll take a closer look at this a bit later. Let's first prepare our database for this chapter.
Preperation
Soon we will learn how to fill our database with sample data using a technique known as 'seeding', but for now we will create some dummy records within our database using Eloquent. We won't use any new functionality here, we will use the skills that we have learned in recent chapters.
First we are going to need to create a migration to build the schema for our sample table. We are going to use music albums as our demo data. Let's create a migration to build an albums table.
$ php artisan migrate:make create_albums
Created Migration: 2013_07_21_103250_create_albums
Now let's fill in the method stubs with the code required to build the schema for our new albums table.
<?php
use Illuminate\Database\Migrations\Migration;
// app/database/migrations/2013_07_21_103250_create_albums.php
class CreateAlbums extends Migration {
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('albums', function($table)
{
$table->increments('id');
$table->string('title', 256);
$table->string('artist', 256);
$table->string('genre', 128);
$table->integer('year');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::drop('albums');
}
}
In the up() method of our migration, we use the Schema facade to create a new table called albums. The table will contain varchar columns for the album title, the artist that performed the music, and the genre of the music. We also have an auto-incremental id field as required by the Eloquent ORM, and finally an integer field to store the release year for the album.
In the down() method we drop the table, restoring the database to its original form.
Let's run our migration to structure the database.
$ php artisan migrate
Migration table created successfully.
Migrated: 2013_07_21_103250_create_albums
Our database now has a structure to hold our sample album data. Now we need to create an Eloquent model definition so that we can interact with our tables rows using PHP objects.
<?php
// app/models/Album.php
class Album extends Eloquent
{
public $timestamps = false;
}
Lovely, simple, clean. We have disabled timestamps on our Album model definition to simplify the examples within this section. Normally I like to add timestamps to all of my models. While there may be a slight performance overhead in doing so, and a little extra storage required, I find that the timestamps are most useful in providing an audit trail for an individual application model.
Now we have all we need to fill our database table with the dummy album data. As I mentioned earlier, ideally we would be using database seeding for this task, but for now we will simply create a routed Closure. As a result we will encounter a little repetition, but we will let it slide this time. We only intend to visit this route once.
<?php
// app/routes.php
Route::get('/seed', function()
{
$album = new Album;
$album->title = 'Some Mad Hope';
$album->artist = 'Matt Nathanson';
$album->genre = 'Acoustic Rock';
$album->year = 2007;
$album->save();
$album = new Album;
$album->title = 'Please';
$album->artist = 'Matt Nathanson';
$album->genre = 'Acoustic Rock';
$album->year = 1993;
$album->save();
$album = new Album;
$album->title = 'Leaving Through The Window';
$album->artist = 'Something Corporate';
$album->genre = 'Piano Rock';
$album->year = 2002;
$album->save();
$album = new Album;
$album->title = 'North';
$album->artist = 'Something Corporate';
$album->genre = 'Piano Rock';
$album->year = 2002;
$album->save();
$album = new Album;
$album->title = '...Anywhere But Here';
$album->artist = 'The Ataris';
$album->genre = 'Punk Rock';
$album->year = 1997;
$album->save();
$album = new Album;
$album->title = '...Is A Real Boy';
$album->artist = 'Say Anything';
$album->genre = 'Indie Rock';
$album->year = 2006;
$album->save();
});
These are some personal favourites of mine. I'm hoping that those of you who aren't fans of punk rock haven't been too offended by music taste, and will continue with the chapter.
As you can see, for each of our dummy rows we create a new instance of an Album model, populate all fields, and save our populated model to the database.
Go a head and visit the /seed URI to fill the albums table with our sample data. You should receive a blank page because we did not return a response from the route.
Now that our sample data has been written to the database, you may delete the /seed route. We don't need it anymore! Our preperation is complete, let's start learning about Eloquent queries.
Eloquent To String
Objects in PHP can optionally include a __toString() method. You may have come across this in the past, it was added in PHP 5.2 along with some of the other double _ underscore prefixed magic methods. This method can be used to control how the object should be represented as a string.
Thanks to this method, our Eloquent models can also be expressed as a string. You see, the Eloquent base class that we extend with our own models contains a __toString() method. This method will return a JSON string that will represent the values of our Eloquent model.
This might sound a little confusing, and it's been a while since we have seen an example. Let's first look at the normal way of exposing the values contained within our Eloquent model instances.
<?php
// app/routes.php
Route::get('/', function()
{
$album = Album::find(1);
return $album->title;
});
In the example above we use the inherited static find() method of our Album model and pass an integer value of 1 to retrieve a model instance representing the album table row with an id column value of 1. Next we return the title attribute of the model instance to be displayed as the response of the view.
If we visit the / URI we receive the following response.
Some Mad Hope
The title of the first dummy album inserted into our database, as expected. Now let's modify the route to instead return the model instance itself as a response from the routed Closure.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::find(1);
});
Let's visit the / URI to examine the response.
{"id":1,"title":"Some Mad Hope","artist":"Matt Nathanson","genre":"Acoustic Rock","year":2007}
That looks like JSON to me! All JSON strings created by the framework have all extra whitespace and indentation removed to save bandwidth when transfering the data. I'm going to manually beautify all of the JSON examples within this chapter, so don't be surprised if your own outputs look a little more jumbled than the ones displayed in this chapter.
Let's beautify the above output.
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
}
Laravel has executed the inherited __toString() method of our Eloquent model instance to represent its values as a JSON string. This is really useful when creating RESTful API's that serve JSON data. It's also a great way of expressing the output of our queries for the rest of the chapter.
Some Eloquent methods will return a number of model instances as a result, instead of the single model instance returned by the above example. Let's take a quick look at the all() method, which is used to retrieve all rows as Eloquent model instances.
<?php
// app/routes.php
Route::get('/', function()
{
$albums = Album::all();
foreach ($albums as $album) {
echo $album->title;
}
});
We use the all() method of our Album model to retrieve an array of Eloquent model instances that represent the rows of our albums table. Then we loop through the array, and output the title for each of the Album instances.
Here's the result that we receive from the / URI.
Some Mad HopePleaseLeaving Through The WindowNorth...Anywhere But Here...Is A Real Boy
Great, those are all of the album titles. They are all stuck together because we didn't insert a HTML line break <br /> element. Don't worry about it, at least we retrieve them all.
I'm really sorry about this, but, once again I have lied to you. If you previously used Laravel 3, the concept of a retrieval method returning an array of model instances will be familiar to you. However, Laravel 4 doesn't return an array from such methods, instead, it returns a Collection.
I don't believe you. If it doesn't return an array then how did we loop through the results?
That's simple. The Collection object implements an interface which allows for the object to be iterable. It can be looped through using the same functionality as standard PHP arrays.
Hmm, I see. I'm not going to take the word of a lier so easily, though.
Ah I see, you require additional proof? Let's dump the $albums attribute to see what we are working with. This should do the trick.
<?php
// app/routes.php
Route::get('/', function()
{
$albums = Album::all();
var_dump($albums);
});
When we visit the / URI we receive the following response.
object(Illuminate\Database\Eloquent\Collection)[134]
protected 'items' =>
array (size=6)
0 =>
object(Album)[127]
public 'timestamps' => boolean false
protected 'connection' => null
protected 'table' => null
protected 'primaryKey' => stri
... loads more information ...
Woah, you weren't lying this time!
As you can see, the result of any methods that return multiple model instances is represented by an instance of Illuminate\Database\Eloquent\Collection. You can see from the output of var_dump that this object holds an internal array of our model instances called items.
The advantage of the collection object is that it also includes a number of useful methods for transforming and retreiving our model instances. In a later chapter we will examine these methods in more detail, but for now it's worth knowing that the Collection object also includes a __toString() method. This method functions in a similar manner to the one on our model instances, but instead creates a JSON string that will represent our model instances as a multi dimensional array.
Let's return the Collection object that is the result of the all() method as the response for our routed Closure. Like this:
<?php
// app/routes.php
Route::get('/', function()
{
return Album::all();
});
The response that we receive after visiting the / URI is as follows.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
},
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
We receive a JSON string containing an array of objects that represent the values of our individual albums.
So why are we learning about the __toString() functionality now? We aren't intending to build a JSON API in this chapter, are we? No, we're not quite ready for that yet.
You see, I can use the JSON output to display the results of the queries that we will be executing throughout the rest of the chapter. It will be more readable than a bunch of foreach() loops for our result sets. Now you know exactly why these results are being outputted as JSON. Everyone's a winner!
Now that we have our database filled with dummy data, and have identified a way of displaying query results, let's take a look at the structure of Eloquent queries.
Query Structure
Eloquent queries are used to retrieve results based on a number of rules or criteria. You don't always want to retrieve all of your album rows. Sometimes you will only want to retrieve the discography for a single artist. In these circumstances we would use a query, to ask for only rows that have a title column value of the artist that we desire.
Eloquent queries can be broken down into three parts.
- The model.
- Query Constraints
- Fetch methods.
The model is the model instance that we wish to perform the query upon. All of the examples within this section will be forming queries based upon the Album model.
Query constraints are rules that are used to match a subset of our table rows. This way we can return only the rows that we are interested in. The most familiar constraint used with SQL is the WHERE clause.
Finally, we have the fetch methods. These are the methods that are used to perform the query, and return the result.
Let's take a look at the structure of an Eloquent query in its simplest form.
<?php
Model::fetch();
All of our queries act upon one of our Eloquent models. The constraint methods are entirely optional, and in the above example, are not present. Next we have a fetch method. This method doesn't exist, we're just using it to demonstrate the shape of a query. The first method of a query chain is always called statically, with two colons ::.
Eloquent queries can consist of no constrains, a single constraint, or many constraints. It's entirely up to you. Here's how a query will look with a single constraint.
<?php
Model::constraint()
->fetch();
Notice how the constraint is now the static method, and our fetch method has been chained onto the end of the first method. We can add as many constraints to the query as we require, for example:
<?php
Model::constraint()
->constraint()
->constraint()
->fetch();
Constraints are entirely optional, but all queries must begin with a model, and end with a fetch method. Here's an example using our Album model.
<?php
Album::all();
The Album is our model, and all() is one of our fetch methods, because it is used to retrieve the result of our query.
Fetch methods can be used to return either a single model instance, or a Collection of model instances. However, as we discovered earlier, both can be expressed in JSON format as the response of a routed Closure or controller action.
We know that our query constraints are optional, so let's start by looking at the variety of fetch methods that are available.
Fetch Methods
Let's begin with some of the fetch methods that you might have encountered within previous chapters. First we have the find() method.
Find
The find() method can be used to retrieve a single Eloquent model instance by the id column of its row. If the first parameter of the method is an integer, then only a single instance will be returned. Here is an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::find(1);
});
We want to retrieve the database row with an id of 1, so only a single model instance is returned.
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
}
If we instead provide an array of id values, we receive a Collection of model instances.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::find(array(1, 3));
});
Here is the result. A collection containing the model instances that represent rows with an id column value of 1 and 3.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
}
]
All
The all() method can be used to return a collection of model instances that represent all rows contained within the table. Here is an example of the all() method.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::all();
});
We receive a collection containing instances of all of the Albums contained within our database.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
},
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
First
In circumstances where a collection of model instances will normally be returned, the first() fetch method can be used to retrieve the first model instance stored within. It's very useful if you would rather that a query return a single instance, rather than a collection of model instances. Without a constraint, first() will simply return the first row in the database table.
Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::first();
});
We receive a single model instance, the first album stored within our database table.
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
}
Update
We don't have to simply retrieve model instances, we can also change them. Using the update() method we can update the values of the table rows that are the result of the Eloquent query. Simply pass a key-value array as the first parameter to the update() method to change the column values for each row. The array key represents the name of the column to change, and the value represents the new intended value for the column.
The update() method is special, and cannot be used without a constraint, therefore I will use a simple where() constraint within the example. If you don't understand it, then don't worry about it. We will cover constraints in detail within the next section. Here's an example that will modify our albums table. (Don't worry, I will restore it for the next example.)
<?php
// app/routes.php
Route::get('/', function()
{
Album::where('artist', '=', 'Matt Nathanson')
->update(array('artist' => 'Dayle Rees'));
return Album::all();
});
We update the artist field of all rows with an artist of Matt Nathanson, changing its value to Dayle Rees. The update() method doesn't retrieve model instances, so instead we return a collection of all model instances using all().
[
{
id: 1,
title: "Some Mad Hope",
artist: "Dayle Rees",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Dayle Rees",
genre: "Acoustic Rock",
year: 1993
},
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
As you can see, I'm now a rockstar. Awesome!
Delete
Much like the update() method, the delete() method will not return any instances. Instead, it will remove the rows that are the result of the query from the database table.
<?php
// app/routes.php
Route::get('/', function()
{
Album::where('artist', '=', 'Matt Nathanson')
->delete();
return Album::all();
});
We query for all albums that have an artist column value of Matt Nathanson then we use the delete() method to delete their rows from the database.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
The albums by Matt Nathanson have been removed from our database. Which is a real shame, because he makes beautiful music!
Here's a quick tip. If you intend to delete all table rows for a specific model, you might find the truncate() method to be more descriptive.
Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
Album::truncate();
return Album::all();
});
As you can see, all of our table rows have vanished!
[ ]
Get
Get is the most important of our fetch methods. It is used to retrieve the result of the query. For example, if we use a where() constraint to limit a result set to a single artist, then it wouldn't make any sense to use the all() trigger method. Instead we use the get() method to retrieve a model instance collection.
Confused? Here's the get() method in combination with a where() constraint.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Something Corporate')
->get();
});
We receive a collection of model instances that have an artist column containing a value of Something Corporate.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
}
]
The get() method has an optional parameter. You can pass an array of column names to it and the result objects will only contain values for those columns. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Something Corporate')
->get(array('id', 'title'));
});
We pass an array with the values id and title to the get() method, here is the result set that we retrieve.
[
{
id: 3,
title: "Leaving Through The Window"
},
{
id: 4,
title: "North"
}
]
As you can see, only the columns that we requested are present within the results.
Pluck
The pluck() method can be used to retrieve a value for a single column. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::pluck('artist');
});
The first and only parameter, is the name of the column that we wish to retrieve the value for. If the query matches multiple results, then only the value of the first result will be returned. Here's the result that we receive from the above example.
Matt Nathanson
Lists
While the pluck() method will retrieve only one value for a specific column, the lists() method will retrieve an array of values for the specified column across all result instances. Let's clarify this with an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::lists('artist');
});
Once again, the lists() method accepts one single parameter. The name of column that we wish to retrieve all values for. Here is the result from our query.
[
"Matt Nathanson",
"Matt Nathanson",
"Something Corporate",
"Something Corporate",
"The Ataris",
"Say Anything"
]
As you can see, we have retrieved the values contained within the artist column for all of our table rows.
ToSql
Right, this one isn't exactly a fetch method, but it is really useful! You can use the toSql() method anywhere where you would normally use a fetch method, typically the end of a query chain, and it will return the SQL that represents the query as a string.
Let's look at an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Something Corporate')
->toSql();
});
Similar to the previous example, but this time we call toSql() instead of get(). Here's the result we receive.
select * from `albums` where `artist` = ?
Very useful for debug indeed!
C> What's the question mark for?
Laravel's query builder uses prepared statements. This means that the question marks are placeholders that will be replaced with your actual values or 'bindings'. The benefit is that your bindings will be escaped before replaced into the string, to avoid an SQL injection attempt.
Now that we have discovered the fetch methods, it's time to learn about how to add rules to our queries.
Query Constraints
The fetch methods from the previous chapter are useful for retrieving model collections and instances from our database. However, sometimes we need to fine tune the result to only a few specific rows. That's when query constraints become useful.
In mathematics, set based arithmatic allows us to capture a subset of a much larger set of values. This is essentially what we are trying to accomplish using query constraints, however, I have also snuck some transformation methods into the chapter to change the ordering of results.
Let's get started with a method that represents the most common SQL query constraint, the WHERE clause.
Where
The first constraint method that we will examine is the where() method. If you have used SQL in the past, then you will likely have come across the WHERE clause for retrieving table rows by matching the value of their columns.
Let's lead with an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Matt Nathanson')
->get();
});
We use the where() method to limit the results to albums which have an artist column value of Matt Nathanson only.
The where() method will accept three parameters. The first parameter is name of the column that we wish to perform the comparison on. In this example we wish to perform the comparison on the artist column. The second parameter is the operator to use for the comparison. In our example we wish to ensure that the artist column is equal to a value, so we use the equals = symbol.
We could have used any of the other common comparison operators supported by the SQL, such as <, >, =>, =<, etc. Experiment with operator types to retrieve the results that you require.
The third parameter is value that we will compare with. In our example we wish to ensure that the artist column matches Matt Nathanson, so, in this instance Matt Nathanson is the value.
Once again, the where() method is only a query constraint. We will use the get() method to retrieve a Collection of results. Let's take a look at the response from the / URI.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
}
]
Excellent. Both albums from the database that have an artist column value of Matt Nathanson were returned to us. This would be useful if we intended on providing sections of a music website for displaying the discographies for an individual artist.
It's worth remembering that the get() and first() methods are interchangable. Let's alter the existing example to retrieve only the first instance that matches the provided condition.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Matt Nathanson')
->first();
});
Now the query will only retrieve a single model instance representing the first row that is a match to the provided constraint. Here is the result from the / URI.
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
}
Let's try another operator with the where() method. How about LIKE? The LIKE SQL operator can be used to compare parts of a string by using a percentage % symbol as a wildcard.
Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('title', 'LIKE', '...%')
->get();
});
In the above example, we would like to retrieve all rows with a title field that starts with three period characters .... The percentage % sign will let the database know that we don't care what value comes after our triple period. As a side note, I know that a triple period is known as an ellipsis, I just thought this might be easier for readers who don't have English as a first language.
Let's take a look at the result from the / URI.
[
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
We receive a result collection for the albums titled '...Anywhere But Here' and '...Is A Real Boy', both of which start with a triple period, and are fabulous albums.
We aren't limited to a single where() method call within a query. We can chain multiple where() methods together to retrieve rows based on a number of different criteria. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('title', 'LIKE', '...%')
->where('artist', '=', 'Say Anything')
->get();
});
In the above example, we wish to find rows that have a artist column that starts with a triple period and an artist column that is equal to 'Say Anything'. The and is important. Both constraints must match for a row to exist within the result set.
Here's the result from the above example.
[
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
A collection containing a single model instance, an album with a title that begins with a triple period and an artist value of 'Say Anything'. We receive a Collection containing Say Anything's '...Is A Real Boy' album. One of my personal favourites!
OrWhere
We don't always need both constraints to match. Sometimes a match for either condition is good enough for us. In sithations such as this, we can use the orWhere() method. In fact, most of the constraints within this chapter will have an alternative version prefixed with or that will allow an alternate constraint to match. For this reason I won't provide separate sections for the or method variations in future.
As always, here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('title', 'LIKE', '...%')
->orWhere('artist', '=', 'Something Corporate')
->get();
});
We provide an initial constraint, stating that the album title must begin with a triple period (yes, I know it's called an ellipses). Then we include an orWhere() method that states that our result set can also consist of results which have an artist column value of Something Corporate.
Let's take a look at the result.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
We receive a Collection of result instances that have an album title that starts with a triple period or artist columns with a value of Something Corporate.
You can chain together as many where() and orWhere() methods as you need to filter your table rows down to the required result set.
WhereRaw
The whereRaw() method can be used to provide a string of SQL to perform a WHERE condition on the result set. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereRaw('artist = ? AND title LIKE ?', array(
'Say Anything', '...%'
))
->get();
});
The whereRaw() method accepts a string of SQL as its first paramater. All question ? marks within the string are replaced by array elements from the second parameter to the method in sequential order. If you have ever bound properties to a prepared statement with SQL then this syntax will be familiar to you. The values provided will be escaped to avoid SQL injection attacks.
Once the query builder has performed the neccesary transformation, the resulting SQL will look like this:
artist = 'Say Anything' AND title LIKE '...%'
The result of our query is as follows.
[
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
You can use the whereRaw() method in circumstances where you require complex SQL in addition to your where() type constraints. Just like the where() method, the whereRaw() method can be chained multiple times and with other constraint methods to limit your result set. Once again the orWhereRaw() method is included to allow for alternate conditions.
WhereBetween
The whereBetween() method is used to check that the value of a column is between two provided values. It's best described with an example, in fact, I think everything is. Strange, right? Maybe it's because Laravel code tends to speak for itself!
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereBetween('year', array('2000', '2010'))
->get();
});
The first parameter to the whereBetween() method is the name of the column that we wish to compare. The second parameter is an array of two values, a starting value and a limit. In the above example, we are looking for albums that have a release year between 2000 and 2010. Here are the results.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
The result is as expected, a number of albums from the 2000s.
Just as with the other where() type methods, you can chain as many as you need to, and as always, we have an orWhereBetween() alternative method.
WhereNested
The whereNested() method is a clean way of applying multiple where constraints to a query. Simply pass a Closure as the first parameter to the method, and give the Closure a placeholder parameter named whatever you like. I like to name mine $query.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereNested(function($query)
{
$query->where('year', '>', 2000);
$query->where('year', '<', 2005);
})
->get();
});
Within the Closure you may apply as many where() type constraints or orWhere() type constraints to the $query object, which will then become part of your main query. It just looks a whole lot neater! Here's the result set from the above example.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
}
]
Note that there is no orWhereNested() alternative to this method, but here's the secret... you can also pass a Closure to orWhere(). Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereNested(function($query)
{
$query->where('year', '>', 2000);
$query->where('year', '<', 2005);
})
->orWhere(function($query)
{
$query->where('year', '=', 1997);
})
->get();
});
We wish for an album to have a release year between 2000 and 2005 or have a release year of 1997. Here's the SQL that is generated from the above method.
select * from `albums` where (`year` > ? and `year` < ?) or (`year` = ?)
These are the results from the above query.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
}
]
WhereIn
The whereIn() method can be used to check that a column value exists within a set of values. It's really useful when you already have an array of possible values to hand. Let's take a look at how it can be used.
<?php
// app/routes.php
Route::get('/', function()
{
$values = array('Something Corporate', 'The Ataris');
return Album::whereIn('artist', $values)->get();
});
The first parameter to the whereIn() method is the column that we wish to perform the comparison on. The second value is the array of values to search within.
The resulting SQL from the above query looks like this.
select * from `albums` where `artist` in (?, ?)
Here's the collection of results that we receive from the example query.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
}
]
The whereIn() method also has the usual method alternative in the form of orWhereIn() and can be chained multiple times.
WhereNotIn
The whereNotIn() method is the direct opposite to the whereIn() method. This time you provide a list of values, and the column value must not exist within the set.
Let's take a look at an example.
<?php
// app/routes.php
Route::get('/', function()
{
$values = array('Something Corporate', 'The Ataris');
return Album::whereNotIn('artist', $values)->get();
});
Once again, we pass the comparison column as the first parameter, and our array of values as the second parameter.
Here's the resulting SQL.
select * from `albums` where `artist` not in (?, ?)
Finally, here's the result set from our sample query. All of the albums that aren't identified by the artists within our values array.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
Once again, the orWhereNotIn() is also available as an alternative.
WhereNull
The whereNull() constraint can be used when you need to retrieve rows that have a column value of NULL. Let's check an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereNull('artist')->get();
});
The single parameter for the whereNull() method is name of the column that you wish to contain a null value. Let's take a look at the generated SQL for this query.
select * from `albums` where `artist` is null
Now let's take a look at the result set for the query.
[ ]
Oh that's right, we don't have any NULL values in our database! I don't want to rewrite this chapter again, so you will have to use your imagination here. If we had an artist column with a value of NULL then its row would appear in the result set.
Yes, you guessed it! The orWhereNull() method is also available.
WhereNotNull
The whereNotNull() method is the opposite of the whereNull() method, so this time we should be able to see some results. It will return rows that have a column value that isn't equal to NULL. Let's take a closer look.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereNotNull('artist')->get();
});
The first, and only parameter to the method is the column name. Here's the generated SQL for the query.
select * from `albums` where `artist` is not null
Here is the result set matching the example query.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
},
... 4 more ...
]
All of the albums in our database. This is because none of the artist columns have a value of NULL.
Once more, the orWhereNotNull() method is available to perform an or type query.
OrderBy
The orderBy() method can be used to order the results returned by your query by the value of a specific column. Let's dive right in with an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Matt Nathanson')
->orderBy('year')
->get();
});
The first parameter to the orderBy() method is the name of the column that we wish to order by. By default, the ordering will be in ascending order.
Here's the generated SQL.
select * from `albums` where `artist` = ? order by `year` asc
Here's the result set from the query.
[
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
},
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
}
]
Great, our albums have been returned in ascending order by release year. What if we want them to be descending? Don't worry, Laravel's got you covered!
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Matt Nathanson')
->orderBy('year', 'desc')
->get();
});
We add a second parameter to the orderBy() method with a value of desc. This tells Laravel that we wish to retrieve our results in descending order. Here's the generated SQL.
select * from `albums` where `artist` = ? order by `year` desc
Now here's the updated result set.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
}
]
Switcheroo! Our results are now in descending order.
You can use the orderBy() clause with any combination of the constraints within this chapter. You can also use additional orderBy() methods to provide additional ordering, in the order that the methods are provided.
Take
The take() method can be used to limit the resultset. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::take(2)
->get();
});
The first parameter to the take() method is the amount of rows that you wish to limit by. In the above example we only wish for the query to return two result objects.
Here's the SQL that is generated by the query.
select * from `albums` limit 2
Finally, here is the result set that we receive.
[
{
id: 1,
title: "Some Mad Hope",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 2007
},
{
id: 2,
title: "Please",
artist: "Matt Nathanson",
genre: "Acoustic Rock",
year: 1993
}
]
Take can be used in combination with any of the other query constraints. Mix and match!
Skip
When using the take() method, the skip() method can be used to provide an offset for the query result set. Here's an example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::take(2)
->skip(2)
->get();
});
The skip() method accepts a single parameter to provide an offset. In the above example, the first two rows will be disgarded from the result set. Here is the generated SQL.
select * from `albums` limit 2 offset 2
Here's the result set that we receive.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
}
]
As you can see, the first and second rows have been skipped, starting at the third row in the database.
Magic Where Queries
Right, now time for something magical! By now, you must be more than familiar with the where() query. The where() query is responsible for restricting a column to a certain value within your result set. Here's an example to remind you.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('artist', '=', 'Something Corporate')
->get();
});
We intend to retrieve a result set where the artist column of each row is equal to Something Corporate. It's a nice clean way of restricting the database rows to our desired result set. Could it get any cleaner? Well, as it happens, yes it could! We could use the magical where query syntax.
Take a close look at the following example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereArtist('Something Corporate')->get();
});
Wait, what's that whereArtist() method? We didn't learn about that in our query constraints chapter. Well, this method is a little special. First let's visit the / URI to see the result.
[
{
id: 3,
title: "Leaving Through The Window",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
},
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
}
]
It appears to function in a similar manner to our where() method with an equals = operator. Right, time to explain what's happening. You see, the where() equals query is likely the most common query of all, and because of this, Taylor has provided a convenient shortcut.
You can simply append the name of the column that you wish to query on to the where() method. First you must capitalize the first letter of the field that you wish to compare. In our example, we used the artist column, so the resulting method name is whereArtist(). If our field name is snake cased, for example, shoe_size, then we must uppercase the first letter of each word to whereShoeSize().
The only parameter to the magical where() method is the expected value of the column. Let's see another example.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::whereTitle('North')->get();
});
Fetch all albums, with a title column value of North. Here's the result from the query.
[
{
id: 4,
title: "North",
artist: "Something Corporate",
genre: "Piano Rock",
year: 2002
}
]
Wonderful, there's our album! Be sure to remember the magical where() query if you ever find yourself wanting to retrieve ORM instances by column values.
Query Scopes
Query scopes can be very useful if you find yourself repeating the same queries over and over again. Let's begin with an example. Remember those two albums with names beginning with triple periods? '...Is A Real Boy' and '...Anywhere But Here'. Let's imagine that fetching albums that begin with a triple period is a common action within our application.
Let's see, we could query for the albums every time, like this.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::where('title', 'LIKE', '...%')->get();
});
That would be awfully repetetive though, wouldn't it? We don't want to be repeating ourselves. Why don't we use a Query Scope? Let's get started. First let's revisit our Album model. Right now it looks like this.
<?php
// app/models/Album.php
class Album extends Eloquent
{
public $timestamps = false;
}
Let's add a new method to this model. Now our model looks like this.
<?php
// app/models/Album.php
class Album extends Eloquent
{
public $timestamps = false;
public function scopeTriplePeriod($query)
{
return $query->where('title', 'LIKE', '...%');
}
}
We've added the scopeTriplePeriod() method to our model. It's a special method with a specific function, it will help us re-use common queries. All scope methods begin with the word scope, and then an identifier. The method will accept a single parameter, a $query object. This object can be used to construct queries like the ones that we discovered in the previous sections. In our example, we use the return statement to return the value from our where() method. The where() method takes the same shape as our previous example.
Now let's switch back to our routing file. Let's alter our existing query. Here's the new routed Closure.
<?php
// app/routes.php
Route::get('/', function()
{
return Album::triplePeriod()->get();
});
We change our where() query to instead call the triplePeriod() scope. Then we simply call get() on the result to retrieve the results. Note that the scope part of the method name is not included, be sure to leave that out of you method calls! Let's take a look at the result.
[
{
id: 5,
title: "...Anywhere But Here",
artist: "The Ataris",
genre: "Punk Rock",
year: 1997
},
{
id: 6,
title: "...Is A Real Boy",
artist: "Say Anything",
genre: "Indie Rock",
year: 2006
}
]
Great, that's the result set we were expecting. Use as many scopes as you need to reduce your repetition.
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!