There are two ways we can run select queries in Drupal 8, and they both work similarly to the way they did in Drupal 7. We have the D8 equivalents of db_query() and db_query_range() and the equivalent of db_select(). This will look familiar to D7 developers. In Drupal 8, these procedural functions still exist, but, in a deprecated state. This only means that instead of using the old functions, we should use the connection service I mention next.
The first type of select queries are typically more performant because we construct them by writing the SQL statements ourselves (with placeholders, of course), whereas the db_select() type of query is an OOP query builder that still needs to transform a chained object construct into an SQL statement. However, don't let this performance be a real deciding factor, because as you can imagine, the impact is minimal. Also, the query builder is the more proper way of running queries, because they are alterable (can be deconstructed).
Also, the first type of select query is typically used for simpler queries, but if you are an SQL guru, it can actually be faster and easier to write a complex query using that method. Moreover, they rely on developers, ensuring that the SQL statement is compatible with the underlying database. So, it is up to you which of the two types you choose, considering all of these factors.
Let's first take a look at how we can run a basic query against our tables using the db_query()-like method. We'll then see how the same query can be run using the other way:
$database = \Drupal::database();
$result = $database->query("SELECT * FROM {players} WHERE id = :id", [':id' => 1]);
This is a simple SQL statement, albeit a bit funky if you have not done any D7 development. We passed the query to the first parameter of the query() method of the connection object as a string whereas we would pass an array of placeholder values for the second parameter. These are found throughout the SQL string proceeded by a colon (:id) and are later replaced with the value that maps to the same key in the placeholder values array. Another thing to note is that the table name in the query is surrounded by curly braces. This is because in reality, table names can be prefixed when the site is installed, and our code should not concern itself with that prefix. Drupal will prepend it automatically.
Now, let's take a look at how we can run the same query using the query builder:
$result = $database->select('players', 'p')
->fields('p')
->condition('id', 1)
->execute();
This time, we will use the select() method on the connection object to get our hands on a SelectInterface instance with which we can build our query. We need to pass the table we want to query to it, as well as an alias for that table. This is particularly important when performing joins. Then, we use the fields() method to specify which of the table columns we want to retrieve. The first parameter is the table alias, whereas the second (optional) is an array of column names. Ll columns will be included (*). Next, we have a single condition being applied to the query for the column id and value 1. The third optional parameter is the operator that defaults to =. Lastly, we execute the query and get the same result as with the preceding example.
You will immediately note, if you remember, that the structure of this query builder is very similar to the Entity Query, and the components are also chainable to a certain extent, as we will see. Handling the result
Both of the preceding queries return a StatementInterface, which is traversable. So, to access its data, we can do this:
foreach ($result as $record) {
$id = $record->id;
$team_id = $record->team_id;
$name = $record->name;
$data = $record->data;
}
Each item in the loop is a stdClass, and their property names are the actual names of the columns returned, while their values are the column values.
Alternatively, the StatementInterface also has some fetcher methods that can prepare the results for us in different ways. These mostly come from the parent \PDOStatement class, which is native PHP. The simplest is fetchAll():
$records = $result->fetchAll();
This returns an array of stdClass objects as we saw before, so it does all the looping to extract the records for us. If we want this array keyed by the value of a field in the record, we can perform the following:
$records = $result->fetchAllAssoc('id');
This will use the value in the id field to key the array.
If we're expecting single records, we can also use the fetch() method, which returns only one such object (the next one in the result set); fetchObject() does the same thing. More complex select queries
Let's create a more complex query now, to join our team table and retrieve the team information in the same record as the player:
$result = $database->query("SELECT * FROM {players} p JOIN {teams} t ON t.id = p.team_id WHERE p.id = :id", [':id' => 1]);
This will return the same record as before, but inclusive of the values from the matching team record. Note that since we have a join, we had to use table aliases here as well. There is one problem with this query, though--since both tables have the name column, we cannot use the * to include all of the fields, as they will get overridden. Instead, we need to include them manually:
$result = $database->query("SELECT p.id, p.name as player_name, t.name as team_name, t.description as team_description, p.data FROM {players} p JOIN {teams} t ON t.id = p.team_id WHERE p.id = :id", [':id' => 1]);
As you can see, we specified the fields from both tables we wanted to include, and we indicated different names as aliases where there was a name conflict. Now, let's write the same query using the query builder:
$query = $database->select('players', 'p');
$query->join('teams', 't');
$query->addField('p', 'name', 'player_name');
$query->addField('t', 'name', 'team_name');
$query->addField('t', 'description', 'team_description');
$result = $query
->fields('p', ['id', 'data'])
->condition('p.id', 1)
->execute();
$records = $result->fetchAll();
First of all, not all methods on the query builder are chainable. The join() method (and the other types of join methods, such as innerJoin(), leftJoin(), and rightJoin()) and the addField() method are prominent examples. The latter is a way we can add fields to the query by specifying an alias (we cannot do it via the fields() method). Moreover, the condition() field is also prefixed with the table alias it needs to be in (which was not necessary before, when we didn't use a join).
For all the other methods useful for building queries, go to SelectInterface and ConditionInterface. They are typically well documented in there.
No comments:
Post a Comment