0

Can I modify the SQL generated by the Views Query Builder? Views doesn't support subquery out of the box, so I'm just wondering if I can add the final line to my query manually?

Background info: The query I'm trying to perform looks a little like this:

select full_name, email, users.uid
from
cloudsponge
inner join 
`users` on users.uid = cloudsponge.importer_uid
where cloudsponge.email not in (select mail from users)

It's worth pointing out that all tables have millions of rows so performance is also important (I haven't checked this query on a large dataset yet).

0

try hook_query_alter. Views queries should pass though this hook allowing you to alter them. https://api.drupal.org/api/drupal/modules!system!system.api.php/function/hook_query_alter/7

  • Under Advanced Options in Views you can supply a Tag to later use in an IF block in hook_query_alter .... – tenken Jul 9 '13 at 4:19
  • This returns a query object, before being compiled into SQL. How can I edit the actual SQL after compilation? – cjm2671 Jul 9 '13 at 13:56
  • You don't. Drupal 7 has a database abstraction layer. You will want to call functions on the query object. – 2pha Jul 11 '13 at 7:10
  • I am going to assume it is a selectquery object, if so the class is outlined at: api.drupal.org/api/drupal/includes!database!select.inc/class/… – 2pha Jul 11 '13 at 7:11
0

As you want to make changes in the query generated by Drupal Views, you can take help of creating a 'Views Custom Filter'. I personally have tried it for adding an extra WHERE condition to the Views query. You have some other options available in 'handler' folder in 'Views' module.

You shall be needed these files in your custom module :

YOUR_MODULE_NAME.info, YOUR_MODULE_NAME.module, YOUR_MODULE_NAME.views.inc, HANDLER_NAME_YOU_ADD_IN_(YOUR_MODULE_NAME.views.inc).inc.

Hope it helps

0

OK, I solved this by hacking it. This is NOT the cleanest solution, but I see it as being the cleanest way out. I ended up creating a the query I want with MySQL's CREATE VIEW syntax. I then loaded the MySQL VIEW into Views as a table; this did the trick. It's not pretty, but it's the best solution I could come up with for my purposes.

  • how did you do? I'm struggling doing the same with drupal 8! – Asy Feb 11 '17 at 18:11

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.