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 * 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 more information about all the other methods useful for building queries, go to SelectInterface and ConditionInterface. They are typically well-documented in there.