1

I'm currently troubleshooting an issue where anonymous users and certain roles see a different set of results from a view than administrators and roles with more permissions do. The view is supposed to have two node results, but for anonymous and others, there is only one.

I am trying to get the full SQL of the final query that's creating the anonymous pages. I'm using hook_views_query_alter(), and I can get the full query using dpq($view->query->query()); when I'm logged in as an administrator.

However, when I'm an anonymous user, or one of the other roles encountering the bug, I get SQL like this: ((taxonomy_index.status = :db_condition_placeholder_7) AND (node_field_data.type IN (:db_condition_placeholder_8))).

I tried getting the placeholder values from a technique I used in drupal 7, using the arguments() method, but apparently that doesn't exist in Drupal 8.

But moreover, I would like to avoid matching up the string db_condition_placeholder manually, because I could make a mistake, and misdiagnose what the source of the problem is. I want the full query string that drupal sends to the database, so that I can debug it directly with the MySQL server.

I tried other techniques listed here, such as ->addTag('debug'), which apparently did nothing, and kint($view->query->query());, which only returns output for administrator users.

How can I get the full query string from a view executed for anonymous users?

  • Maybe try turning off SQL Rewriting? I dislike that it's the default as I usually am using views to get around access restrictions anyway. You can still place query based filtering to do simple access anyway. – mradcliffe Oct 3 '18 at 1:37
  • If kint($view->query->query()) works, then take $view->query->query() and simply try to echo it (maybe with one of the methods listed in the output kint($view->query->query()) gave you). – leymannx Oct 3 '18 at 6:01
  • To display the query using kint($view->query->query()), go to the permission page for devel and enable kint for anon users. – pwaterz Oct 3 '18 at 13:21
  • @mradcliffe Disabling SQL rewriting worked. Could you please post that as an answer? I'd like to accept it : ) – user1359 Oct 4 '18 at 13:47
  • Sure, I didn't think it was exactly an answer to the specific question, but it probably makes sense to add it, @user1359. Thank you. – mradcliffe Oct 4 '18 at 19:53
2

Note: This does not answer the question of "trying to get the full SQL of the final query that's creating the anonymous pages", but answers the underlying issue.

tl;dr Checking the Advanced Settings > Query > Disable SQL Rewriting option will allow views to return consistent results.

Explanation

The views module (contrib and core) configures views with restrictive entity access enabled by default. This sends the query object through hook_db_rewrite_sql (6), hook_query_TAG_alter (7, 8). Nodes for instance will add in joins to the node_access table, which can be prohibitively expensive on queries.

This is important for sites that use complex entity access where one node may be hidden from view based on node grants/access rights.

However query results will be different when querying entities (such as nodes) depending on if you are logged in. This can be frustrating when building a view as a site builder - what you see is not what you get.

It may not necessary to use SQL rewriting because a view has other access restrictions. Checking the Advanced Settings > Query > Disable SQL Rewriting option will allow views to return consistent results.

This is usually the first thing I do when creating a view unless I specifically need the extra access checks because

  1. It improves performance significantly.
  2. I create selective and filtered views that naturally restrict data using filters, contextual filters and view access restrictions.

Update 2019.06

Note that the views module changed the label for this setting from "Disable SQL Rewriting" to "Disable Access Checks".

  • The option Disable SQL Rewriting is unchecked by default. Did you mean to word that Checking the Advanced Settings > Query > Disable SQL Rewriting option? – prkos May 24 at 15:44
  • Yes, thank you, @prkos. – mradcliffe May 24 at 17:38

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.