Querying using Fluent in laravel

Laravel provides many ways to access databases. If we choose not to write raw SQL
statements, we can use the Fluent query builder to make things easier.

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 and add a method to
get the shows:
class Show {
public function allShows($order_by = FALSE,
$direction = ‘ASC’)
{
$shows = DB::table(‘shows’);
return $order_by ? $shows->order_by($order_by,
$direction)->get() : $shows->get();
}
}
5. In our routes.php file, create a Show route to display the information from
the model:
Route::get(‘shows’, function()
{
$shows = new Show();
$shows_by_rating = $shows->allShows(‘rating’, ‘DESC’);
dd($shows_by_rating);
});

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. Our model has one
method to display all the shows in our table, with optional parameters if we’d like to reorder
how they’re displayed.
Our route instantiates the Show model and runs the allShows() method. To display the
results, we use Laravel’s dd() helper function. We could also create a view and pass the
data there to loop through.

 

Leave a Reply

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