|

How To Use The Laravel Query Builder

How To Use The Laravel Query Builder

We’re moving forward with Laravel and so far we have a good grasp of routing, views, and we have also set up our environment with a database connection. We also included phpMyAdmin as a graphical front end to the database so that manually working with tables and data is easy. Recall that in our passing data to views tutorial, we simply hard coded the data in the Closure of our routes file. We’ll take it even further in this episode, and being working with dynamic data from a database.


Create A Database Table

We’ll continue on this path of working with video games for our examples. We already set up the database, but we don’t have a table yet to store the information about our games. We are going to need one, so lets use Artisan to create a table for us and we’ll do this by creating a migration. So we know we want to create a migration, therefore let’s learn a bit more about it by typing php artisan help make:migration at the terminal. From the help instructions we see, let’s provide a name for the migration as well as a name for the table we want to create. When naming the migration, the convention is to describe what the migration does using underscores. We want to create a games table so our name of the migration will therefore be create_games_table. We also specify an option of –create so that we can also provide the name of the table itself we want to create. The table is going to hold many games, so we name it games in the plural. So we specify the name of the table like this snippet here --create="games". Putting this all together, the full command to create our migration is as follows:

php artisan make:migration create_games_table –create=”games”

laravel create migration with a table name

After we run our command, we have a new migration.
migrations have their own folder

When we open the file, it has the following code to help get us started.

At this point, we do need to edit the file ourselves to match the data we are trying to hold. By default, the table already has an id column and a timestamps column. Almost all tables will use these two, so we leave them in place. What we don’t have yet are the specific columns for the title of a game, the publisher of a game, and the releasedate of a game. It is in the up() function where we can define database columns, so let’s do that now.


Migrating The Database

We’ve created the migration, or schema, for the new database table that we want to create. It does not yet exist in the database however. This is because we need to complete a two step process. Step one is to create the migration file itself. Step two is to actually run the migrations so that the code in the file executes and creates the database table for us. We do this by typing php artisan migrate.
php artisan migrate

We can now verify the table exists, both via console and the gui.
viewing the new games table
new games table in phpmyadmin


Add Data To The Database Table

We’ll put some data into the database manually first. We can insert a few games like so.
insert data into database with phpmyadmin

Once we insert a few of these games, we just browse the table to see our data.
listing records in the database with phpmyadmin

Pretty Cool! We now have Mega Man 2, Metroid, and Tecmo Bowl as some sample data to work with. So let’s get cooking with the Laravel Query Builder to get access to this data.


Behold the DB class

We will now update our routes file so that it makes a call to the database and fetches the records from the table we just created. This is how we can accomplish that goal.


How To Examine Collection Contents

The code above actually stores a Laravel Collection in the $games variable. In order to understand how to process that collection, we must first inspect it. This can be done a few ways.


Die and Dump The Output

dd laravel collection


Render As JSON

laravel collection rendered as json


Passed To The View Then Run Through print_r

routes/web.php

resources/views/games.blade.php

inspecting collection with print_r

Excellent. We can see that $games is an instance of, or object, of the class Illuminate\Support\Collection. Contained in the collection object is an array. This array holds one or more objects of type stdClass. So a collection is an array of objects. By looking at one of the ways to inspect this collection that we just demonstrated, we can figure out how to access individual values in the collection.


Get the title of the third game


Get the publisher of the first game


Get the release date of the second game


Rendering The Collection

With our understanding of how to inspect and reach the individual data elements in a collection, we can now create a proper view to render all of our games in the database.


routes/web.php


resources/views/games.blade.php

collection rendered to an html table


Applying a where clause

You don’t usually just fetch all the records in a database. You likely will need a subset of the data available. Here we apply a where clause using the query builder to fetch only the record where the title is Metroid.

query builder where clause

The Query Builder in Laravel is ridiculously powerful. Pretty much any type of query you can make in MySql, you can do with the query builder. In addition, you can simply use raw queries if you need to get super granular.


Adding a View for Individual Records

When a user is presented with a list of resources on a website, they can usually click on any given item to drill down to find more information about it. We will set this up now with our games. We are going to need to modify our first route, modify our original view file, add a second route, and also add a second view file to accomplish this. We will also create a new directory in our views folder. It will be called games, and it will hold all views that have to do with displaying data about games.


routes/web.php

This is our updated routes file. There are a few things to note. In the /games route, notice that the query builder is making use of a latest() method. This fetches all games and orders them by the most recently added to the database. That’s a nice little feature. Next up, notice that first argument to the view() function. It is specified as games.index. What this means is, Laravel should look in a games folder contained within the views folder to find the index.blade.php file.

Moving on to our new route of /games/{id}, we find a new syntax here. The {id} portion of the route is a wildcard operator so that an id can be passed to the query via the URL which is used in the browser. Our route closure then captures that id, and uses it with the find() function to get a particular record. Additionally, the view() function in this route is having games.show passed as the first argument. Again this means Laravel will look in the games folder located in the views folder for a file named show.blade.php. Let’s create those new view files now.


resources/views/games/index.blade.php


resources/views/games/show.blade.php

Check out the fruits of our labor!
laravel in action


How To Use The Laravel Query Builder Summary

The query builder in Laravel is very powerful and will allow you to construct almost any type of query you would need in an application. It provides a fluent implementation of commands, so you can string together logic in single statements. It uses PDO parameter binding to ensure all queries to the database are safe from attack. In this tutorial, we started by creating a table to hold the games we would be working with. We did this by creating a new migration file with Artisan, and then running that migration to create the new table. From there we used phpMyAdmin to insert some data manually into our games table, so that we could test out a few basic queries with the query builder. We saw that queries with multiple results create a collection. A collection in Laravel is a special type of construct which contains an array of objects. In order to work with those results we took a good look at three different ways to examine the contents of a collection. Replicating our web page that displayed a table of games and their associated properties was covered next. Finally, we started to examine how to break out view files into a common directory within the views folder so that we could have one view to display all records and another to be used to drill down on a single record.