entityQuery examples for everybody

The Drupal::entityQuery method has been a staple for Drupal developers since the early days of Drupal 8. But without a dedicated drupal.org Documentation page, it can be difficult for new developers to get a really good handle on. 

I've been using the QueryInterface API documentation for a few years now as my go-to source for help with using entityQuery, but a couple of weeks ago I stumbled on a feature of entityQuery that made me wonder what else, if anything, I was missing.

This blog post is meant to provide those new to entityQuery with some commonly used examples, as well as a request for those experienced with entityQuery to let us know what else is possible.

The basics

entityQuery allows developers to query Drupal entities and fields in a SQL-like way. A Drupal site's tables and fields are never a 1-to-1 match for the site's entities and fields - entityQuery allows us to query the database as if they were 1-to-1. 

Much like you could write some SQL that returns all rows of table1 where field1 is 14, using entityQuery you can ask Drupal to return all entities of type "basic page" where the value of field_some_field is 14. 

Developers can write an entityQuery using the following method to return the QueryInterface:

$query = \Drupal::entityQuery(string $entity_type_id);

Example 1: Simple node entity queries 

This first example returns all nodes of type page where the value of field_some_field (an integer field) is 14.

$query = \Drupal::entityQuery('node')
  ->condition('type', 'page')
  ->condition('field_some_field', 14);
$results = $query->execute();

This example shows that an entityQuery is built up by adding various conditions, sorts, ranges, and other qualifiers. Note that the $query methods are all chainable - each one returns the $query, so we can add multiple conditions per line. 

For conditions, the default operator is "=", so in both of the conditions shown above, the EntityQuery is looking for type=page and field_some_field=14. If we wanted to find all basic page nodes where field_some_field > 14, then the entityQuery would look like this:

$query = \Drupal::entityQuery('node')
  ->condition('type', 'page')
  ->condition('field_some_field', 14, '>');
$results = $query->execute();

Sorts and ranges can also be easily added:

$query = \Drupal::entityQuery('node')
  ->condition('type', 'page')
  ->condition('field_some_field', 14, '>')
  ->sort('nid', ASC)
  ->range(0, 10);
$results = $query->execute();

Finally, we can save ourselves some typing by calling execute() directly on the $query:

$results = \Drupal::entityQuery('node')
  ->condition('type', 'page')
  ->condition('field_some_field', 14, '>')
  ->sort('nid', ASC)
  ->range(0, 10)
  ->execute();

The execute() method returns an array of IDs for the entities found. For node entities, this is the node ID. If this was an entityQuery of users, then the ID would be user ID.

Example 2: Condition groups

While the first example covers many real-world use cases, another pattern that is often seen is that of condition group. Consider the use case where we want to find all users whose account was created either before the year 2010 or since January 1, 2020.

In this case, we can create an oConditionGroup as part of the entityQuery, as well as adding additional conditions, sorts, and ranges:

$query = \Drupal::entityQuery('user');
$group = $query
  ->orConditionGroup()
  ->condition('created', '1262304000', '<')     // Jan 1, 2010
  ->condition('created', '1577836800', '>');    // Jan 1, 2020
$results = $query->condition($group)
  ->condition('status', 1)
  ->sort('created', DESC)
  ->execute();

Example 3: Reaching into reference fields

My latest entityQuery() discovery is the fact that it can be used to query field values of referenced entities. This means that if you have two node entities:

Event

  • Node ID
  • Name
  • Date
  • Location (reference field)

Location

  • Node ID
  • Name
  • Address
  • Venue type - List (text) field

Then you can use entityQuery to return all event nodes whose location's "venue type" is a particular value.

$results = \Drupal::entityQuery('node')
  ->condition('type', 'event')
  ->condition('field_location.entity:node.field_venue_type', 'boat')
  ->execute();

Note: I recently wrote about this in a quicktip as well.

Example 4: Show me the SQL

During DrupalCamp Asheville I presented a short mostly-unplanned session on this topic during the unconference. Thanks to Hussain Abbas, Kristen Pol, and others I learned how easy it was to see the SQL that entityQuery actually uses to return the list of entity IDs - it's really quite easy. For example, to output the SQL from the previous example, use:

$query = \Drupal::entityQuery('node')
  ->condition('type', 'event')
  ->condition('field_location.entity:node.field_tag.entity:taxonomy_term', 'sailboat')
  ->__toString();

Resulting in:

SELECT base_table.vid AS vid, base_table.nid AS nid
FROM
node base_table
INNER JOIN node_field_data node_field_data ON node_field_data.nid = base_table.nid
INNER JOIN node__field_location node__field_location ON node__field_location.entity_id = base_table.nid
LEFT OUTER JOIN node node ON node.nid = node__field_location.field_location_target_id
INNER JOIN node__field_venue_type node__field_venue_type ON node__field_venue_type.entity_id = node.nid
WHERE (node_field_data.type = 'event') AND (node__field_venue_type.field_venue_type_value = 'boat')

Example 5: How deep can we go?

Let's go back to example 3 - is it possible to create a condition that queries a field value of an entity that is referenced by and entity that is referenced by the entity you are querying on?

Consider the use case where we want to find all events whose location has a term whose name is "sailboat". Turns out that it is:

$results = \Drupal::entityQuery('node')
  ->condition('type', 'event')
  ->condition('field_location.entity:node.field_tags.entity:taxonomy_term.name', 'sailboat')
  ->execute();

Gotchas

It is important to understand that the entityQuery will only return entities that the user has access to - access control is performed by default. If you want to disable access control, then add the following to the query:

$query->accessCheck(FALSE);

Did I miss anything? What else can be done with entityQuery? Feel free to leave a comment and let me know.
 

Comments

Thanks for the great article. There are about 3 typos all on the same line:

->condition('field_some_field', 14 '>');

I think you forgot a comma

Submitted by anonymous (not verified) on Fri, 07/31/2020 - 02:05

Add new comment