What Query Builder is, along with a guide to using it in the PHP Laravel 5.1 framework.
As we already pointed out in , there are several ways of accessing the data, with different levels of abstraction. Query Builder is one of them. Its level is not as low as , but it is closer to the database management system than the ORM would be.
Query Builder contains a series of functions ready to perform the most common operations with a database, but without using the SQL language directly, but rather the Laravel API. In this article we are going to see a complete introduction to the Laravel 5.1 Query Builder, which will help us to know how to work with the system. But before we begin, it is worth noting a couple of important features.
- Query Builder works with all types of databases supported by Laravel. Therefore, the code that we will use can be executed for any compatible manager, obtaining the same results. Therefore, this system allows to abstract from the database engine that we are using below.
- Query Builder internally uses PDO mechanisms, including parameter binding in queries. Therefore, it is not necessary to filter the data that we are going to use in the statements, since these will be automatically filtered to protect us from SQL injection attacks.
As you will see, the use of the Eloquent ORM simplifies even more the work with the databases, however Query Builder manages to complete the functions in a shorter amount of time. For a small data set or number of operations that time will be imperceptible, but as the load goes up you will see that Query Builder is a bit faster.
Fluent objects Query Builder
To work with the Query Builder we still require the use of the “DB Facade”, but at this time, instead of using the methods we covered in the previous article, we will use the table() method.
We pass the name of the table with which we intend to operate as a parameter to the table() method and it returns an Illuminate\Database\Query\Builder class object, which is commonly known as “Fluent Query Builder” or simply “fluent”.
$fluent = DB::table(‘books’);
In $fluent we now have a reference to a “Fluent Query Builder” object that has been linked to the “books” table. That object will already be linked to work on the indicated table and we will be able to pass messages to it (invoke its methods) to do the precise operations that we need at all times on that table.
Note: don’t forget to do the corresponding “Use DB;” in case you are working with this facade from a namespace in which that class is not known, for example from a controller.
Now we can call other methods to perform different operations. For example, get() to receive data from a table.
$books = $fluent->get();
This will return an array of elements, where in each box we will have references to objects, of class stdClass, with the data of a book.
Note: stdClass is a built-in class in native PHP that is used when casting a variable (that is not an object type) to an object, for example, by casting an associative array to an object. That is, it is a “generic” object to put it in some way, which does not do anything in particular.
These two operations are usually chained together in a single line of code:
$books = DB::table(‘books’)->get();
Apart from get(), there are other methods to convert a Fluent Query Builder object into a data structure, for example first() will return the first element of the collection that contains a fluent object.
$book = DB::table(‘books’)->first();
In this case, you do not receive an array of books as return, but a single book in a stdClass object.
The difference between get() and first() is that in the first you get an array with an indeterminate number of elements and in the second you get an object with a single element. To verify this, we give you two pieces of code that will perform the same task. Only the way to access the first element to retrieve its name differs.
// If I use first() I access the record columns via an object $book = DB::table(‘books’)->first(); echo $book->name; // if I use get() I get an array, whose first record is indexed with $books = DB::table(‘books’)->get(); echo $books->name;
This is a very small part of what can be done with the Query Builder, but it is the beginning of all kinds of more complex operations that we are going to see. Getting the most out of it is based on chaining methods that help us to do the things we need. It is therefore essential to have the , which is the version that we use in this manual.
Selection operations with Query Builder
When doing select operations you have the option of specifying the fields you want to retrieve with the select method:
$books = DB::table(‘books’)->select(‘name’)->get();
If there is more than one field that you want to select, you can send an array with the fields that you want to retrieve.
$books = DB::table(‘books’)->select()->get();
Now let’s see how to select a specific group of users, with the where() method, which we chain to the creation of the fluent object.
$books_mario_puzo = DB::table(‘books’)->where(‘author’, ‘=’, ‘Mario Puzo’)->get();
In this code we output a fluent query builder object with a restricted set of items from the “books” table. We get the entire table and then we chain the where() method to it indicating that we need those books with “author” equal to “Mario Puzo”.
The where method can receive any type of operator, including “like”.
$books = DB::table(‘books’)->where(‘author’, ‘like’, ‘%july%’)->get();
If we want to make two conditions, for example that the author is such and that the title is such and such, we can chain two where() methods.
$books = DB::table(‘books’)->where(‘author’, ‘=’, ‘Mario Puzo’)->where(‘name’, ‘like’, ‘%padrino%’)->get (); Note: If you want to increase the readability of so much message chaining, you can write this on multiple lines. $books = DB::table(‘books’) ->where(‘author’, ‘=’, ‘Mario Puzo’) ->where(‘name’, ‘like’, ‘%padrino%’) ->get ();
If we want to combine several where() with the logical “or” function, for example authors named “Mario Puzo” or “Cervantes”, you can do it with the orWhere() method.
$books = DB::table(‘books’) ->where(‘author’, ‘=’, ‘Mario Puzo’) ->orWhere(‘author’, ‘=’, ‘Cervantes’) ->get() ;
You have many other options like whereNull(), whereNotNull(), wherein() whereNotin(), whereNotBetween().
For example, the code above (authors being one or the other) could have been written:
$books = DB::table(‘books’) ->wherein(‘author’, ) ->get();
Joins with Query Builder
If we want to join two or more tables with Query Builder we also have the possibility of using the “join” operations, with the join() method invoked on the Fluent Query Builder object.
This method receives several parameters:
- The table to join
- Table 1 field by which they are related
- Relation operator, usually “=”
- Field of table 2 by which they are related
- Join type, defaults to “inner”
- Boolean, positive to do a “join where”, default is false
The key parameters are the first 4, which we will see in use in the following example. Given a table of invoices and a table of customers, where the invoices have a customer_id.
$customerinvoices = DB::table(‘customers’) ->join(‘invoices’, ‘invoices.id_customer’, ‘=’, ‘customers.id’, ‘inner’, true) ->select(‘customers.* ‘, ‘facturas.id as id_factura’, ‘facturas.fecha’) ->where(‘clientes.email’, ‘=’, ‘miguel@’) ->get();
You can join more than two tables, specifying the different Joins. We are going to add a “billable item” table to the previous scheme, where each invoice has an indeterminate number of billable items, which would be the concepts of the invoice.
$facturasCliente = DB::table(‘clientes’) ->join(‘invoices’, ‘invoices.id_cliente’, ‘=’, ‘clientes.id’) ->join(‘item_facturables’, ‘invoices.id’, ‘=’, ‘item_facturables.id_factura’) ->select(‘customers.*’, ‘invoices.id as id_invoice’, ‘invoices.date’, ‘concept’) ->where(‘customers.email’, ‘= ‘, ‘miguel@’) ->get();
There are several types of join and also several methods to do joins such as leftJoin(), rightJoin, joinWhere(), etc. There is also a variant of the join method that allows you to configure the relationship through an anonymous function. For all these uses, consult the documentation.
Inserts with Query Builder
To insert one or more fields into table records we can launch the insert() message to a Fluent Query Builder object. We obtain the object as before, with the table() method of the DB facade, indicating the name of the table: DB::table(‘books’). We then chain the call to the insert() method, as follows:
$inserted = DB::table(‘books’) ->insert();
The call to the insert() method returns a boolean indicating whether it was able to complete the insert successfully.
Now we can see another alternative call to insert(), sending an array of arrays as a parameter to insert several elements at once.
$inserted = DB::table(‘books’) ->insert( , ]);
If we want to know the Id of the inserted record, we have the alternative of using the insertGetId() method, as can be seen in the following code:
$id = DB::table(‘books’) ->insertGetId();
Updates with Query Builder
Making an update is very simple and similar to what we have already done. We call the update() method on the Query Builder, to which we pass an associative array with the data to update. This command is often combined with the Where method, to restrict the record or group of records that you want to update with this data.
$updates = DB::table(‘books’) ->where(‘id’, ‘=’, ’74’) ->update();
In this case, as the return value of update we will obtain the number of records that were updated when the query was executed.
As an additional utility, there are some shortcuts to perform an increase or decrease operation, applicable to numeric fields. Instead of handwriting the code to do the update, call the increment() or decrement() method. The field to be increased and decreased is sent as the first parameter and optionally a second parameter can be indicated with the increment or decrement units.
$num_updates = DB::table(‘books’) ->where(‘id’, ‘=’, ’97’) ->increment(‘reads’);
deletes
Deletes are exactly the same as updates, only you have to call the delete() method for deletion.
$deletes = DB::table(‘invoices’)->delete();
Don’t forget to place the where() method to restrict the records that will be deleted! If what you want is to delete all the elements of a table, and restore its initial state without data, you also have the truncate() method.
Simple debugging with toSql() in Query Builder statements
Select statements can be easily debugged…
Facebook Comments
Disqus