Get pure SQL from Drupal::entityQuery

Question

Given the following code, how can I print the SQL that is generated?

$bundle='my_bundle_type';
$query = \Drupal::entityQuery('node');
$query->condition('status', 1);
$query->condition('type', $bundle);
$entity_ids = $query->execute();

What I have tried:

• __toString() doesn't work
• dpq($query) throws an error

Answer 1

With debug tag (devel module is mandatory) we can print the raw sql.

$query = \Drupal::entityQuery('foo')->condition('bar', 'xyz')->addTag('debug');
$ids = $query->execute();

Answer 2

UPDATE

My original answer is not valid anymore, now there's a good solution for this, see Sukhjinder Singh answer. In short: use Devel module and add tag 'debug', execute it and query will be printed.

$entityQueryObject->addTag('debug')->execute();

ORIGINAL ANSWER

As @kiamlaluno says, the problem is \Drupal::entityQuery() implements QueryInterface, not SelectInterface. dpq needs its first argument to implements that SelectInterface.

The object returned by \Drupal::entityQuery() is a Drupal\Core\Entity\Query\Sql\Query. This object has a protected property that implements the SelectInterface:

  /**
   * The build sql select query.
   *
   * @var \Drupal\Core\Database\Query\SelectInterface
   */
  protected $sqlQuery;

Unfortunately, the Query objects don't allow to access the $sqlQuery or print the SQL string.

As a very quick and dirty hack you can edit the Query class file in core/lib/Drupal/Core/Entity/Query/Sql/Query.php, change the $sqlQuery property to be public, and access it directly.

In Query.php, protected $sqlQuery should be public $sqlQuery;.

Then you can use the dpq function from devel module with the internal property. Make sure the devel module is installed (so it's enabled).

Prints a SQL string from a DBTNG Select object. Includes quoted arguments.

So in your case:

$sql_string = dpq($query->sqlQuery, TRUE));

Or just:

kint(dpq($query->sqlQuery, TRUE));

dpq() Parameters

• object $query: An object that implements the SelectInterface interface.

• boolean $return: Whether to return the string. Default is FALSE, meaning to print it and return $query instead.

• string $name: Optional name for identifying the output.

IMPORTANT!!

This is a dirty trick, you should revert the Query.php file when you are done. Hacking Drupal core is a very bad practice and kittens, ponies and other puppies are suffering when you do that. Maybe even some unicorns are - better think twice about it.

Final thoughts

This a dirty workaround. Another better approach may exist but I'm not aware of it. If there is not a better way Drupal core may add some functionality to get the SQL string as sometimes is needed when debugging, and frankly, this hack is not a good way to do it.

Answer 3

With installed Devel module you can get the string with raw SQL query from EntityQuery class via:

$rawSqlQuery = (string) dpq($entityQuery);
echo $rawSqlQuery;

Answer 4

Here's another approach:

How can I debug entity queries in Drupal 8?

See the question in this link. This is a solution that has worked for me, if you add those two functions to a custom module, all you have to do is tag an entity query with "debug" to output the string version of the sql query.

Answer 5

You can simply use the __toString() option as below and look at the SQL in the $query variable.

 $query = \Drupal::entityQuery('node')
->condition('status', 1)
->condition('type', 'basic_page')
->condition('changed', $utc_start, '>=')
->__toString();

Answer 6

You can use ReflectionClass in this case.

$reflection = new ReflectionClass(get_class($query));
$property = $reflection->getProperty('sqlQuery');
$entity_ids = $query->execute(); // this is required
$sql = (string)$property->getValue($query);
echo $sql;

Answer 7

Enable Devel and Devel Kint modules then just try

 kint ($query);