Select queries

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 means that instead of using the old functions, we should use the connection service I will mention next.

The first type of select query is 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).

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 string as the first argument to the query() method of the connection object. The second argument is an array of placeholder values for this query string. 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, 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. All columns will be included (*). Next, we have a single condition being applied to the query for the id column and the 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.