7

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
10

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();
| improve this answer | |
  • 3
    This isnt working for me. I have Devel enabled. – Jeff Dec 5 '18 at 2:43
  • works for me, thanks. – 2pha Mar 13 at 6:59
8

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.

| improve this answer | |
  • Unfortunately, as I stated in the question the dpq throws error, I think it doesn't work with \Drupal::entityQuery – Oleg Videnov Mar 14 '17 at 10:40
  • The OP said that function throws an error. He didn't say which error, exactly, so it could be the Devel module has not been installed. – kiamlaluno Mar 14 '17 at 10:40
  • 1
    I think the problem is the first argument dpq() is expecting: The object returned from \Drupal::entityQuery() implements QueryInterface, not SelectInterface. The first doesn't require the classes implementing it to have an addExpression() method, for example. – kiamlaluno Mar 14 '17 at 10:51
  • 1
    @downvoter, please explain the down vote. If you know a proper way to get the SQL string please share with us, I'm very interested in do it without this multiple-times-said dirt and quick hack. – sanzante Mar 14 '17 at 18:33
  • 2
    Tweaking core is always a hack, the API should allow this. Seems like the Query class itself needs a _toString() if it wants to keep $sqlQuery protected. – Grayside Aug 30 '17 at 21:56
1

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.

| improve this answer | |
  • so why you don't mark answer as accepted yet if it works for you ? :D – Yuseferi Mar 15 '17 at 19:39
  • I'm talking about the question. The answer that suggests webprofiler isn't what I'm looking for. Webprofiler is slow. I just want to output the sql query for a specific query, not every query. – oknate Mar 15 '17 at 19:58
-2

Enable Devel and Devel Kint modules then just try

 kint ($query);
| improve this answer | |
  • This outputs the query object in a readable fashion, not the pure sql that would be generated by the query. – oknate Mar 14 '17 at 20:44
  • @oknate you can find raw query in kint output – Yuseferi Mar 14 '17 at 20:47
  • Awesome, can you add instructions to your answer on how to do that? – oknate Mar 14 '17 at 20:56
  • I tried using kint, unfortunately this eats my memory on a simple query. – Oleg Videnov Mar 15 '17 at 18:15
  • @OlegVidenov yes, it's good debugging tool but memory hungry. – Yuseferi Mar 15 '17 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.