A Better Entity Query

Published at

April 6, 2020

Author

Erlend ter Maat

Tags

Drupal, Drupal entities, Maintainable code

---

Drupal offers a couple of ways to get data from the database. When retreiving data from the database it can be a challenge, when queries get complex, to keep code clean, and keep a good performance of the system.

Views

First there is the views module. It provides a web interface to editors to create both the query and the presentation of a dataset. The power of views is that you can mix any field of any related entity in the destination overview - and you have a lot of tools to customize the output.

Views are best maintained from the web interface. In order to make it maintainable the editor of the view is able to customize the labels of filters, fields, etc. The downside is that it tends to get slow, for it involves a lot of overhead - in generating html code and the query that views glues together is not designed with speed in mind, and hard to debug in case you want something a little different.

Pro’s

• You are able to make the view a little more readable by choosing custom names for fields, filters and relations

Cons

• Performance can be an issue

• Hard to debug the queries it generates

• Reuse of filters is only possible if exactly the same data is required

• Source code output is not readable/editable code (it is yaml, at best)

Direct Queries

When performance is important a lower level interface to entities is available: Entity query. To query the database for entity data some understanding of how drupal stores the entities and fields in the database is required. In Drupal you can build entities in a nice web interface. You can give (unique) names to entities, bundles (for an entity you can define one or more bundles) and fields (for a bundle you can define one or more fields).

Under the hood a database table is created for each field on a bundle like this:

[entity-system-name]__[field-system-name]
- bundle
- entity_id
- [field-system-name]_value

Entity query

The entity query is a wrapper around the storage of the entity. At the time of writing there is no support for other systems then hierarchical databases, but from the perspective of an entity query it should not matter if the entity is stored in MySQL, PostgreSQL or Elastic Search. You can talk to it in terms that you also define at the entity configuration interface.

$query = Drupal::entityQuery('virus');
$query->condition('type', 'biological_virus');
$query->condition('field_name', "Corona");

// NOTE: It requires 'field_name' instead of 'virus__field_name.field_name_value'.

In this way you can query drupal entities. It has a better performance because it only wraps the storage - it does not care about relations between entities, it only cares about system names of entities, bundles and fields.

Pro’s

• Best performance at query execution time.

• Easier to debug.

Cons

• No web interface

• Custom output formatting is required.

Custom Entity Query

In order to write readable, maintainable code that fits tighter to the problem domain/language of the application that you are building and offers more control over the performance I recommend a custom query builder. This is what it could look like, following the example from the above picture.

class VirusQueryBuilder {

    public function __construct() {
        $this->query = Drupal::entityQuery('virus');
        $this->storage = Drupal::entityTypeManager()->getStorage('virus');
    }

    public function filterBiologicalVirus() {
        $this->query->condition('type', 'biological_virus');
        return $this;
    }

    public function filterName(string $name) {
        $this->query->condition('field_name', $name);
        return $this;
    }

    public function all() {
        return $this->storage->loadMultiple($this->query->execute());
    }

}

// It results in this fashion of writing:
$query = new VirusQueryBuilder();
$items = $query
            ->filterBiologicalVirus()
            ->filterName("Corona")
            ->all()

Pro’s

• Allows to talk to the entity storage in more natural way

• Best performance at query execution time.

• Easier to debug.

Cons

• No web interface

• Custom output formatting is required.

Conclusion

Though views provides a handy interface to quickly setup overview pages of entities, when it gets to performance and coding there is great value in using the backend tools that Drupal provides.