Querying using Eloquent ORM in laravel

Laravel provides many ways to interact with databases. One of the easiest ways is using the
Eloquent ORM. It provides a simple and intuitive way to work with data.

Getting ready

For this recipe, we’ll be using the table created in the Creating data tables using migrations
and schemas recipe.

How to do it…

To complete this recipe, follow these steps:
1. In the command prompt, create a migration so we can add some data:
php artisan migrate:make add_data_to_shows_table
2. In our app/database/migrations directory, find a file similar to
2012_01_01_222551_add_data_to_shows_table.php, and add some data
using the Fluent query builder:
class AddDataToShowsTable {
/**
* Make changes to the database.
*
* @return void
*/
public function up()
{
$data1 = array(‘name’ => ‘Doctor Who’,
‘rating’ => 9, ‘actor’ => ‘Matt Smith’);
$data2 = array(‘name’ => ‘Arrested Development’,
‘rating’ => 10, ‘actor’ => ‘Jason Bateman’);

$data3 = array(‘name’ => ‘Joanie Loves Chachi’,
‘rating’ => 3, ‘actor’ => ‘Scott Baio’);
DB::table(‘shows’)->insert(array($data1, $data2,
$data3));
}
/**
* Revert the changes to the database.
*
* @return void
*/
public function down()
{
DB::table(‘shows’)
->where(‘name’, ‘Doctor Who’)
->orWhere(‘name’, ‘Arrested Development’)
->orWhere(‘name’, ‘Joanie Loves Chachi’)
->delete();
}
}

3. Run the migration to add the data:
php artisan migrate
4. In our app/models directory, create a file named Show.php that extends
Eloquent:
class Show extends Eloquent{
public function getTopShows() {
return $this->where(‘rating’, ‘>’, 5)
->orderBy(‘rating’, ‘DESC’)->get();
}
}
5. In our routes.php file, create a show route to display the information from
the model:
Route::get(‘shows’, function()
{
$shows = Show::all();
echo ‘<h1>All Shows</h1>’;
foreach ($shows as $show)
{
echo $show->name . ‘ – ‘ . $show->rating . ‘ – ‘
. $show->actor . ‘<br>’;
}

$show_object = new Show();
$top_shows = $show_object->getTopShows();
echo ‘<h1>Top Shows</h1>’;
foreach ($top_shows as $top_show)
{
echo $top_show->name . ‘ – ‘ . $top_show->rating
. ‘ – ‘ . $top_show->actor . ‘<br>’;
}
});

How its Works…

To populate some data in our shows table, we first need to create a migration using the
Artisan command-line tool. In the migration file’s up method, we create three arrays that hold
our values, using the column names as keys. Those arrays are then put into an array and
passed to the Fluent insert function. The down method uses the where() and orWhere()
functions to locate records by their name, and deletes them. Once we run the migration, our
data will populate into the table.
Next, we create a model to interact with the database in the frontend. For this recipe, all we
need to do is extend Eloquent and the ORM will automatically take care of everything else.
We also add in a method that will return all of the top shows.
Our route calls the all() method for our Show ORM object; this will put all the data into
the $shows variable. Then we do a simple loop through the records and display the fields
we want. Next, we get a filtered list by calling the method in the Show model, by only getting
records with a rating greater than 5 and ordered by rating.

Leave a Reply

Your email address will not be published. Required fields are marked *