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).

.. image:: /_static/Entity-bundle-field.jpg

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

.. code-block::

  [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.

.. code-block:: php

    $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.

.. code-block:: php

    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.