Pre-requisites
Different querying methods based on complexity of requirements
Depending on the complexity of your query, your environment requirements (what flavours of SQL you need to support) and the use case (what you're going to do with the results), you should pick one of three different methods.
Note: you should access all of these methods:
- using dependency-injected services wherever possible
- otherwise, if you're writing procedural code, you can use any of the helper methods on
\Drupal - But avoid using the legacy procedural
db_*()functions: these have been deprecated, and are anyway only wrappers for the underlying services.
Worked examples will follow the discussion below, so don't worry if you're not completely sure what they mean at first.
1. Simple queries Connection::query() and Connection::queryRange()
If your query:
- does not involve entities
- has simple syntax that can be detailed in a single string (perhaps with placeholders for variables)
- uses syntax compatible with all versions of SQL you care about
then you can use the database DI service, and call the method ::query().
To permit simple range queries across all SQL variants supported by Drupal core, ::queryRange() will convert any simple query into a LIMIT/OFFSET or TOP query, to only retrieve a maximum number of results for reasons of paging. The return value is an executed implementation \Drupal\Core\Database\StatementInterface, which extends \Traversible and hence can be looped over with foreach.
Simple queries can in theory run any SQL, including UPDATE, DELETE etc. statements. Because of that lack of restraint, then to prevent potential SQL injection attacks you should avoid building complex strings of SQL before passing them into the method; use placeholders for any variables that might need to be different each time you invoke the query:
$result = $query_service->query( // See "further reading" below for explanation of curly brackets. 'DELETE FROM {watchdog} WHERE wid = :wid', // Placeholder replacement. [':wid' => 123] );
Drupal doesn't—can't, really—enforce a programming style of simple and readable strings whenever ::query() is invoked, but it's strongly recommended you should focus on readability when it comes to SQL queries, given the risk of SQL injection.
Procedural equivalent: retrieve the database service using \Drupal::database() or \Drupal::service("database").
2. Complex queries with Connection::select(), Connection::update() etc.
If your query:
- does not involve entities
- is too complex to use the simple method above
then you can use the database service again, but instead call the method relating to the query's main verb: ::select(), ::update(), ::delete() etc.
Each of these methods return different objects, e.g. dynamic select query support is provided by an implementation of \Drupal\Core\Database\Query\SelectInterface. These objects implement all major SQL clauses through their methods, meaning that (depending on logic at the PHP level) the same query can sometimes have different e.g. WHERE conditions:
$query = $query_service->select('watchdog'); // Find by ID? if ($find_watchdog_by_id) { $query->condition('wid', $wid); } // Otherwise, return the most recent entry. else { $query->orderBy('wid', 'DESC')->range(0, 1); } $result = $query->execute();
The return value from any explicit ::execute() call is an executed StatementInterface, as per the simple queries above.
Procedural equivalent: retrieve the database service as for simple queries.
3. Entity queries with QueryFactory::get() or EntityManagerInterface::getStorage()::getQuery()
If your query involves content or config entities, you should use the entity query API, part of the entity API discussed previously.
Both fundamental types of entity can be queried using this API; the return value is either an integer for count queries, or an array of entity IDs:
$all_view_ids = $query_factory->get('view')->execute(); $num_temporary_files = $query_factory->get('file')->condition('status', FILE_STATUS_PERMANENT', '<>') ->count()->execute();
Any code expecting an array of IDs must then itself load any entities.
Procedural equivalent: retrieve the entity query service directly using\Drupal::entityQuery(); alternatively, if you already have the entity manager service using \Drupal::entityManager(), then call ::getStorage()on this. You can then build a new query with ::getQuery.
Example class demonstrating both simple and dynamic queries
Because entity queries were covered when we discussed the entity API, we only demonstrate the first two query styles in this tutorial. So much in Drupal 8 is covered by either the entity API or the configuration (entity) API, so let's consider one of the few remaining tables that aren't: the watchdog table, where logs are stored.
Save the following PHP class to a file src/DatabaseExample.php in the d8api module:
<?php namespace Drupal\d8api; use Drupal\Core\Entity\Query\QueryFactoryInterface; use Drupal\Core\Database\Connection; /** * Example database calls. */ class DatabaseExample { /** * @var Connection * Database connection. */ protected $connection; /** * Implements __construct(). */ public function __construct(Connection $connection = null) { $this->connection = $connection; } /** * Simple non-entity queries. */ public function simpleQuery() { // Get watchdog data from multiple queries, just to be able to demo // more of the functionality of the simple ->query*() methods. $result = $this->connection->queryRange( 'SELECT wid FROM {watchdog} ORDER BY wid DESC', 0, 3 ); foreach ($result as $record) { $full_result = $this->connection->query( 'SELECT * FROM {watchdog} WHERE wid = :wid', [':wid' => $record->wid] ); foreach ($full_result as $full_record) { var_dump($full_record->message); } } } /** * Dynamic non-entity queries. */ public function dynamicQuery() { // Equivalent of the simple queryRange() with extra message field. $result = $this->connection->select('watchdog', 'w') // Alias 'w' comes in useful when many tables are joined. ->fields('w', ['wid', 'message']) ->condition('type', 'd8api') ->orderBy('wid', 'DESC') ->range(0, 3) ->execute(); foreach ($result as $record) { var_dump($record->message); } // Delete rows and report back how many. var_dump("Deleted rows: " . $this->connection->delete('watchdog') ->condition('type', 'd8api') ->execute()); } }
This class contains a DI-compatible __construct() method, and a method for each of the two query styles under consideration.
What you should see
At the command prompt, use Drush to add three messages to the watchdog logs on the d8api channel:
drush php-eval '\Drupal::logger("d8api")->notice("Test 1");
\Drupal::logger("d8api")->notice("Test 2");
\Drupal::logger("d8api")->notice("Test 3");'This should log the messages to the screen:
[notice] Test 1 [notice] Test 2 [notice] Test 3
And, if you have the dblog module turned on, it will also log to the database table.
Next, inject the database service into an instance of the DatabaseExample object and run simpleQuery():
drush php-eval '(new \Drupal\d8api\DatabaseExample(\Drupal::service("database")))
->simpleQuery();'The queryRange() method will loop over the three watchdog items you've just logged, and produce the following repeatable result:
string(6) "Test 3" string(6) "Test 2" string(6) "Test 1"
Do the same for the dynamicQuery() method:
drush php-eval '(new \Drupal\d8api\DatabaseExample(\Drupal::database()))
->dynamicQuery();'Note that, for a change, we've used the the \Drupal::database() method instead here. The response will be almost the same as for the simple query:
string(6) "Test 3" string(6) "Test 2" string(6) "Test 1" string(15) "Deleted rows: 3"
Except that the delete() method has deleted some rows at the end. Your own row count could be greater than 3, if you've played with the events tutorial, because that also logs some messages on the same channel/of the same type.
If you run the dynamic query again immediately, you should just see:
string(15) "Deleted rows: 0"
And if you re-fill the logs with your three watchdog items using the first command-line example shown at the very top of this section, you can then re-run the dynamic queries and see you delete only three rows this time.
If you can see all this, then congratulations! you have successfully accessed the database using both simple and dynamic queries.
