Querying using raw SQL statements in laravel

Laravel provides many ways to access our database. If we have existing queries that we’ve
used before, or if we need something a bit more complicated, we can use raw SQL to access
our database.

Getting ready

For this recipe, we’ll be using the table created in the Creating data tables using migrations
and schema 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 raw SQL:
class AddDataToShowsTable {
/**
* Make changes to the database.
*
* @return void
*/

public function up()
{

$sql = ‘INSERT INTO shows (name, rating, actor)
VALUES (?, ?, ?)’;
$data1 = array(‘Doctor Who’, ‘9’, ‘Matt Smith’);
$data2 = array(‘Arrested Development’, ’10’, ‘Jason
Bateman’);
$data3 = array(‘Joanie Loves Chachi’, ‘3’, ‘Scott
Baio’);
DB::insert($sql, $data1);
DB::insert($sql, $data2);
DB::insert($sql, $data3);
}
/**
* Revert the changes to the database.
*
* @return void
*/
public function down()
{
$sql = “DELETE FROM shows WHERE name = ?”;
DB::delete($sql, array(‘Doctor Who’));
DB::delete($sql, array(‘Arrested Development’));
DB::delete($sql, array(‘Joanie Loves Chachi’));
}
}

3. Run the migration in the command prompt 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’)
{
$sql = ‘SELECT * FROM shows’;
$sql .= $order_by ? ‘ ORDER BY ‘ . $order_by
. ‘ ‘ . $direction : ”;
return DB::select($sql);
}
}

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 a simple SQL insert command,
and pass in three parameters. We then create three arrays, with the values in the same order
as columns in our query. Then we pass the SQL statement variable and array of values to
Laravel’s DB::insert() command. For our down method, we use a SQL delete statement,
searching by the show’s name. 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 re-order
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. At this point, we could pass the data to a view
and loop through it to display.

 

Leave a Reply

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