Figgered it out. Thanks to the api docs and doxy stuff with views and some forum posts and a heckuva lot of experimenting and google searching: (though this one isn't specifically a 'view' - but helpful info anyway)

(This will probably be embarrassingly obvious to api frequenters, but, hope it might help someone, or maybe someone has some advice/corrections to offer)

So I pretty much wanted to completely redo the sql query generated by a view. It's a simple view, two filters: node.type = group (not exposed), and taxonomy.term (exposed). The 'group' nodes have terms from the 'primary' vocab, and terms from the 'other' vocab. Those two vocabs are copies of each other, and they all use term images (term image is a good module!) with the only difference being that I pre-resized the icons so that the 'primary' icons are significantly bigger than the 'other' icons.

But, I wanted to be able to browse the list of groups like this: just have one field, a single-select pulldown pickbox that chooses from among the 'primary' terms (same as the 'other' terms but the widget is actually tied to the 'primary' vocab). When you select a term, return the list of group teasers like this: first show all the groups that list the term among the node's 'primary' terms; within those, list the groups alphabetically. After the 'primary' hits, show the 'other' hits on the same term (that took some learning in SQL - turns out a self-join was the ticket, see below); and again among the 'others' list the groups alphabetically.

Step 1 = futz around with SQL in phpMyAdmin for a while (OK, hours) to get the query working. (Starting point was to cut-n-paste the query as displayed below the views preview)

Here's the query that did the said stuff:
SELECT node.nid AS nid, node.title AS title, term_data.tid AS tid, AS name
FROM node,term_node,term_data,term_data term_data2
WHERE node.type='group' AND term_data2.tid=7 AND = AND term_node.tid in (term_data.tid) AND term_node.nid = node.nid
ORDER BY tid, node.title

Step 2 = figure out where to put this into php code to take best advantage of all the api stuff which I knew nothing about (and still pretty much don't know anything about) - and to make it fairly modular so that upgrades aren't as painful as they could be. This took a lot of learning too; see the first forum topic listed above, it's close but lacks some detail and isn't quite the same solution... I think the one here is what that author was looking for, looks like this is where it was intended to fit into the code.

So, I added this function at the bottom of views.module, which as far as I can tell is where the hook calls have to reside (in your .module file - and I'm not up for making a new module from scratch) (notice the showcase for this hook is in the views/docs/docs.php, called hook_views_pre_execute where like all hooks you replace the prefix 'hook' with the module name, hence this function name:)
(note this uses placeholders for the args as passed in on the ->build_info['query_args'] array - see the code at the bottom to see how the module normally uses the args; syntax was determined by printing the initial query before editing)

// TMG 3-21-09
// call to hook_views_pre_execute to override the query for the groups browser
function views_views_pre_execute(&$view) {
   if($view->name=="groups_list2") {
//   drupal_set_message($this->build_info['query'][1]);
//    drupal_set_message($view->build_info['query_args'][0]);
//    drupal_set_message($view->build_info['query_args'][1]);
      if($view->build_info['query_args'][1]==0) { // <Any> is the current choice, so, show everything
         $view->build_info['query']="SELECT node.nid AS nid FROM node WHERE node.type='%s'";
      } else {
         $view->build_info['query']="SELECT node.nid AS nid, node.title AS title, term_data.tid AS tid, AS name FROM node,term_node,term_data,term_data term_data2 WHERE node.type='%s' AND term_data2.tid=%d AND = AND term_node.tid in (term_data.tid) AND term_node.nid = node.nid ORDER BY tid, node.title";

And the place this gets used in the default views code (I didn't change this part) in includes/ in the 'execute' function:

   * Execute the view's query.
  function execute($display_id = NULL) {
    if (empty($this->built)) {
      if (!$this->build($display_id)) {
        return FALSE;

    if (!empty($this->executed)) {
      return TRUE;

    // Let modules modify the view just prior to executing it.
    foreach (module_implements('views_pre_execute') as $module) {
      $function = $module . '_views_pre_execute';

   $query = db_rewrite_sql($this->build_info['query'], $this->base_table, $this->base_field, array('view' => &$this));
    $count_query = db_rewrite_sql($this->build_info['count_query'], $this->base_table, $this->base_field, array('view' => &$this));

    $args = $this->build_info['query_args'];



Summary: hooks seem pretty swift. Someone should run with this api idea, it's pretty slick... :> also, SQL can do a lot of stuff in just one query, with the multiple joins and sequential joins and such.

Any thoughts of how to optimize the query or the code or both (or to break it out to a separate autoloaded file that wouldn't get clobbered by upgrade) would be appreciated. Thanks to all the folks who programmed this stuff!


mr.alinaki’s picture

Big thanks! Very useful for me. I use it to add "GROUP BY" to my queries.

CyberGhost636’s picture

This solution works like a charm!
I'm using it to generate price ranges on a real-estate website.
This way it's nowhere near being as complicated as some of the resources I found, recommending to create a custom view programatically via PHP in a node itself! ;-)

Thanks a million!

Peters196’s picture

Thanks a million from me too!
However, I can't get it to work! views_views_pre_execute is not being called!
What's more the code

    // Let modules modify the view just prior to executing it.
    foreach (module_implements('views_pre_execute') as $module) {
      $function = $module . '_views_pre_execute';
      $function($this) ;

in is not being executed, presumably because the foreach loop doesn't find any modules.
(If i put some debug code before the foreach statement it executes, but if I put some debug code inside the foreach loop it is not executed!). -- If I understand it correctly it is this bit of code which should cause views_views_pre_execute() to be executed.
I copied Caver456's code to the end of views.module. All I changes was the value of $view->name to the name of my view, and to change the SQL query in the 'else' part of the 'if' statement to what I think I need. I also removed the opening <?php tag which it objected to (the code immediately preceding it was php anyway!)

Like caver456, I don't feel competent enough, yet, to create a new module from scratch, which is why the function is in the views.module file and is called views_views_pre_execute().

Do I need to do anything else to make the code execute? I have not changed anything in the file (apart from temporary debug code), and the only change I have made to views.module is to add the views_views_pre_execute() function at the end of the file as described earlier.
Am I missing something? Can anyone help please!

Luzgan’s picture

Try to flush cache. It should help.

thompcha’s picture

Many thanks, caver456! I looked long and hard before finding your solution.

Here is how it is used to accomplish the task of using a string as a filter value when the Views filter handler changes the filter value to zero:

function views_views_pre_execute(&$view) {
   if($view->name=="frontpage") {
      $view->build_info['query']=preg_replace('/node_data_field_unix_timestamp.field_unix_timestamp_value >= %d/','node_data_field_unix_timestamp.field_unix_timestamp_value >= %s',$view->build_info['query']);

The default behavior for building the query when a string is used as the value for an integer filter is to use '%d' in the query which gets changed to zero before the query is run. The above code replaces this with '%s' which becomes the filter value string before the query is run. This enables you to enter SQL code (i.e. CURDATE(), UNIX_TIMESTAMP) into the filter value input and have it evaluated with the rest of the query. This picture illustrates what I mean:

Notice that my preg_replace() finds and replaces the entire AND clause that implements the filter (node_data_field_unix_timestamp.field_unix_timestamp_value >= %d). This is because I am not very familiar with '%d' or '%s' and I want to be sure that the substitution of one for the other won't occur in other clauses in case they appear in other clauses when I make changes to other parts of the query.

Again, caver456, I can't thank you enough!

netdreamer’s picture

If your view uses pagination, you may also need to update the query used for counting.
If you miss it, items and item count will be completely unrelated and pagination will break.

sapark’s picture

You can move your code to a file called mymodulename.module and put the file in a new folder sites/all/modules/mymodulename (need to be same name), then create a file in the same folder and put the following info:

; $Id$
name = Name you want to show on modules page eg My Module Name
description = Description you want to show on modules page eg Replaces SQL statement for groups_list2 view.
core = 6.x
version = 6.x-1.0

Rename your function in mymodulename.module from views_views_pre_execute to mymodulename_views_pre_execute (.module file, .info file, module directory and module function need to have same name so Drupal can find them).

oddible’s picture

I have this kind of working but how do you specify the fields to output in your view? In the View Fields I had to specify some field so that I could save the view so I added Node: Nid. I can see that my module is changing which Nids are affected in the Block output but my other fields aren't showing up at all. Obviously they won't be in the View UI's Fields select list so I'm not sure how to get fields created in my customized SQL to show up. I'm going to try the 'Custom' type in the Fields ui now.


Yep, that worked, selected Custom field and PHP Code. Then:

print $data->myfieldname;

tranelch’s picture

It sounds as though you had access to a custom field within the view > fields ui that allowed you to enter PHP. I have PHP filter installed, but I am not seeing such a field (I have Custom text and View result counter under the Global fields). Would you (or anyone else who knows) mind explaining how you were able to accomplish this?


tranelch’s picture

For others that have trouble with the custom fields piece, Custom Fields for views is a module at: Thanks oddible.

oddible’s picture

Getting a SQL error with anonymous users. I followed the advice above and added a the count_query for pagination and that worked great when logged in and resolved issues with the pager.

"SELECT COUNT(*) FROM ( full query...
                ) count_alias";

However, now I get an error where the system seems to be using my count_query and adding another SELECT COUNT(*) to the beginning of it as well as the following:

INNER JOIN node_access na ON na.nid = node.nid
WHERE (na.grant_view >=1
AND ((na.gid =0 AND na.realm = 'all') OR (na.gid =0 AND na.realm = 'content_access_author')
OR (na.gid =1 AND na.realm = 'content_access_rid') OR (na.gid =1 AND na.realm = 'forum_access'))

which is where SQL is spitting up the error. What function am I missing here that's recalling the count_query and adding the extra permission checking? The wierd thing is that the data shows up just fine, but it has this error at the top of the page. Looks like a Content_Access module issue. I have Forum access installed but this is a custom content type and in no way related to forums.

gsvitak’s picture

I am having the same problem where the query works as long as I am the admin user. If I am not the admin user, Drupal is adding an Inner join to the node_access table when using the pre hook. I was wondering if you figured out how to solve this problem.


user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((n' at line 20 query: select DISTINCT(node.nid) as nid, node.title as node_title, field_ideapassion_value AS node_data_field_ideapassion_field_ideapassion_value, field_whyideanecessary_value AS node_data_field_ideapassion_field_whyideanecessary_value from node,content_type_idea where (node.nid=content_type_idea.nid) and node.nid in (SELECT content_type_idea.nid FROM content_type_idea RIGHT JOIN ( SELECT content_field_ideainterestcategory.nid,, content_field_ideainterestcategory.field_ideainterestcategory_value, content_field_ideaexperience.field_ideaexperience_value FROM content_field_ideainterestcategory, content_field_ideaexperience WHERE content_field_ideainterestcategory.vid = content_field_ideaexperience.vid AND = AND ( ( field_ideainterestcategory_value = '2' AND field_ideaexperience_value = '1' ) OR ( field_ideainterestcategory_value = '3' AND field_ideaexperience_value = '2' ) ) ) AS match_ideas ON match_ideas.nid = content_type_idea.nid) INNER JOIN node_access na ON na.nid = node.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 4 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 5 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 6 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 7 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 8 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 9 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 10 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 11 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 1 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 13 AND na.realm = 'user_relationship_node_access_1') OR (na.gid = 3 AND na.realm = 'user_relationship_node_access_author') OR (na.gid = 0 AND na.realm = 'og_public'))) LIMIT 0, 10 / on line 1091.

MegaChriz’s picture

I also have this problem.

I can tell you that it has something to with the implementation of hook_db_rewrite_sql. The node-module is implementing this hook, adding the node_access part of the query. However, the node-module isn't doing anything wrong. It's the function db_rewrite_sql() itself which can't rewrite every query properly. After hours of searching I finally discovered that db_rewrite_sql can't handle complex queries.

chill5-0’s picture

I had a similar problem with taxonomy access control - anonymous users weren't able to view the "view". I found the solution for my instance by reading this post:

I changed the views function from views_pre_execute() to views_pre_render() and that seemed to do the trick for anonymous users.

jfinkel’s picture

Thanks for 1) teaching me more about Drupal best practices and 2) saving me hours of effort.

I have three RSS feeds that dump data into a single content type. I have a view that grabs the newest three. But I really wanted a set that was a union of the newest one from each of the feeds.

I created an SQL view that retrieved this set (using the UNION of three queries), and then created a feeditems_views_pre_execute function which simply SELECTS this view. After putting everything in the proper places with proper naming conventions, it works perfectly.

Thanks again!


cvdenzen’s picture

Hello Joel,

Isn't this possible with Views by creating three displays of type Attachment and attach these displays to another display?

And: do you happen to have a simple RSS solution for non-node items (i.c. wishlist items)? My goal is to make a nicely printable wishlist, even if it has hundreds of items (my son is collecting a series of cartoon books).


beautifulmind’s picture

I am trying my hands with hook_views_query_alter(). I think its a better option than using hook_views_pre_execute(&$view)

Is it proper way?

PeterZ’s picture

When implementing, don't forget to comment out the:


near the top.

Or else you'll have the name of your views scrolling by on all your screens that call views.

tranelch’s picture

Sorry to post this twice, but I doubt anyone would see it in the middle of the discussion.

It sounds as though oddible had access to a custom field within the view > fields ui that allowed him to enter PHP. I have PHP filter installed, but I am not seeing such a field (I have Custom text and View result counter under the Global fields). Can anyone explain how he was able to accomplish this?


savageanne’s picture

I am just going down this path now. The module you need is

I just submitted to the issue queue to find out if this module will be available for Drupal 7 or not. The last commit was 24 weeks ago which is disturbing.

fehin’s picture


aac’s picture



roopel’s picture

Thank You, caver456, for your great solution!

By the way, I found an alternative solution that could be useful too:

My task definition (for example only, it can be solved using Views and Table Wizard features barely!):
I have a forum (using Advanced Forum and Nodecomments module).
Then I should use a CCK userreference field SellerUID which referenced to Users with Seller role who have at least one post on current topic thread.

Modules I have used that have directly relation for this solution: Views 6.x-2.8, Table Wizard 6.x-1.x-dev (I hope that Table Wizard 6.x-1.1 must work as well).
Others used modules are not important in this context.

1. I create MySQL View (requred MySQL 5.0.1 or higher) via SQL command CREATE VIEW using SELECT statement of my desired SQL query (using phpMyAdmin or Navicat, for example). This query will be used as template, hence all possible arguments will be hardcoded:

CREATE VIEW view_seller_current_topic AS
SELECT DISTINCT users.uid AS uid
FROM users
INNER JOIN node_comments ON users.uid = node_comments.uid
INNER JOIN users_roles ON users.uid = users_roles.uid
WHERE (users_roles.rid = 4) AND (node_comments.nid = 15)

2. After that, using Table Wizard, I add *users* table, then add MySQL View as a table (*view_seller_current_topic*), select Available Key in this View (*uid* field) for relationship (Available Field in *users* is defined as *uid* by default due *uid* is a Primary Key in *users* table), and add relationship from base table *users* to *view_seller_current_topic* using coresponding field *uid* in both tables. Don't provide default View and set Incorporate related table to manual!
Screen 1
Screen 2
Screen 3

3. Then I add a View (Views View now) of type User, select necessary fields (Users.Uid and Users.Name in my case), next(!) make a relationship to a *view_seller_current_topic* as figured in Screen 4 and Screen 5, and(!) add an argument for User ID (User ID no need an argument by self, but it helps to get access for arguments at all for correctly using of it later, as described below) as figured in Screen 6 and Screen 7.
My Validator PHP Code is here (don't use <?php ?> in Views Validator PHP Code block):

        CREATE OR REPLACE VIEW {view_seller_current_topic} AS
        SELECT DISTINCT users.uid AS uid
        FROM {users}
        INNER JOIN {node_comments} ON users.uid = node_comments.uid
        INNER JOIN {users_roles} ON users.uid = users_roles.uid
        WHERE (users_roles.rid = 4) AND (node_comments.nid = %d)
return False;

Part of this solution is interception of Node ID URL argument for appropriate using of it instead of applying it to User ID.
You could make any manipulations with Default argument type PHP Code and Validator PHP Code, of course.
Using such manipulations can give you really great flexibility!

4. Save my View and then use it.

That's All.

Solution work good for me.

However, there are some important notes about it:
1. This is not a very elegant solution and have a smell of tricky...
But..., it don't take any interference in Views inner logic, so it will have clear and predicted work.
Base table stay untouched, therefore we could consider external MySQL View ONLY like a complex filter applying to the field from the base table.
2. Please use this solution only when you plan to interact with yours own tables or when using standard Views (and CCK) features are insufficient, because using of high-level ORM implemented by Views and others modules is a really good practice and it makes you independent from any changes in Database tables.
3. Please note, that using of MySQL Views must be realized with care about performance issues. MySQL Views, roughly, is a simple saved SELECT queries, but you must care about indexes used when MySQL View make their queries.
Good article about it:

Hope this could help to somebody!

organicwire’s picture


That's an elegant solution. I managed to calculate additional statistics for my nodes using SQL views from different tables. With the table wizard and drupal views I integrated them into my site.

The initial request was that I show in a view every node and its title, author, date, taxonomy terms. Also I wanted to show the number of comments and flags (see flags module). The latter was the most complicated part.

My solution went in a few steps
1) Create an SQL view that collects all information from the tables node, comments, flag_counts (or any table you need)
2) Import the sql view with table wizard (module tw) and mark the primary key of the sql view
3) Also import the table node
4) Create a relationship between the imported tables
5) Edit / add your view. The fields from the sql view will be available there.

Thanks a lot!

fozzy’s picture

I'm a newbie with Drupal, but not with SQL.

So I quickly hitted this Views limitation and was very happy to find your solution. Works pretty well.

Thanks a lot.

robin.puga’s picture

Thanks for the solution. :-)

~ Robin

mths’s picture


fildawg’s picture

I've used the solution presented here and in the comments to create a custom module. The module is called, however the query is not being overridden with my custom query. Here are the contents of my module:

function MSPview_views_pre_execute(&$view) {
        $view->build_info['query']='SELECT FROM_UNIXTIME(roles.expiration,"%m/%d/%Y") AS expiration_date, users.uid AS uid, realname.realname AS realname_realname, realname.uid AS realname_uid, users.mail AS users_mail, node_users_node_data_field_streetaddress.field_streetaddress_value AS node_users_node_data_field_streetaddress_field_streetaddress_value, node_users.nid AS node_users_nid, node_users.type AS node_users_type, node_users.vid AS node_users_vid FROM uc_roles_expirations roles, users users LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = "profile" INNER JOIN users_roles users_roles ON users.uid = users_roles.uid LEFT JOIN realname realname ON users.uid = realname.uid
LEFT JOIN content_type_profile node_users_node_data_field_streetaddress ON node_users.vid = node_users_node_data_field_streetaddress.vid WHERE users_roles.rid IN (7, 8) AND users.uid = roles.uid ORDER BY realname_realname ASC';

Any thoughts as to what I'm missing?


cvdenzen’s picture

In what file did you put this? It should be in a file that views can find, so that file should be called
Did you implement in your MSPview.module the function MSPview_view_api() function? There should be something like this in your .module file:

// Views support is included in includes/views/
define('WISHLIST_VIEWS_DIR', drupal_get_path('module', 'wishlist') . '/includes/views');

* This module is Views 2.0 enabled.
* Implementation of hook_views_api().
function wishlist_views_api() {
  return array(
    'api' => 2,
    'path' => WISHLIST_VIEWS_DIR,

Sure there is no typo in the name (MSPview)?

Hope this might help, although I am not a drupal expert, and certainly not a views expert.


pexxi’s picture

Using :

module_invoke_all('views_pre_execute', $this);

instead of:

// Let modules modify the view just prior to executing it.
    foreach (module_implements('views_pre_execute') as $module) {
      $function = $module . '_views_pre_execute';

may be cleaner.

danielbeeke2’s picture

function queryfix_views_pre_execute(&$view) {
if($view->name=="Klant") {
if (!$view->build_info['query_args'][0] == ""){
$argument = $view->build_info['query_args'][0];
$view->build_info['query']="SELECT node_data_field_voornaam_kind.field_voornaam_kind_value AS node_data_field_voornaam_kind_field_voornaam_kind_value, node.type AS node_type, node.nid AS nid, node.vid AS node_vid, node_data_field_voornaam_kind.field_achternaam_kind_value AS node_data_field_voornaam_kind_field_achternaam_kind_value, node_data_field_voornaam_kind.field_postcode_value AS node_data_field_voornaam_kind_field_postcode_value FROM node node LEFT JOIN content_type_klant node_data_field_voornaam_kind ON node.vid = node_data_field_voornaam_kind.vid
WHERE (node.type in ('klant')) AND
(node_data_field_voornaam_kind.field_voornaam_kind_value LIKE '%s%%') OR
(node_data_field_voornaam_kind.field_achternaam_kind_value LIKE '%s%%') OR
(node_data_field_voornaam_kind.field_postcode_value LIKE '%s%%')";

I am trying to add one variable to the sql multiple times,.

I have one searchfield and want to search it on multiple terms.
I can get it to work but without the 'WHERE (node.type in ('klant'))'
after I added that it doesnt work anymore,.
when I remove the three lines with the variables it filters on the nodetype.


danielbeeke2’s picture

or to put it more clear,

$view->build_info['query']="SELECT node_data_field_voornaam_kind.field_voornaam_kind_value AS node_data_field_voornaam_kind_field_voornaam_kind_value, node.type AS node_type, node.nid AS nid, node.vid AS node_vid, node_data_field_voornaam_kind.field_achternaam_kind_value AS node_data_field_voornaam_kind_field_achternaam_kind_value, node_data_field_voornaam_kind.field_postcode_value AS node_data_field_voornaam_kind_field_postcode_value FROM node node LEFT JOIN content_type_klant node_data_field_voornaam_kind ON node.vid = node_data_field_voornaam_kind.vid WHERE node.type IN ('klant') AND (node_data_field_voornaam_kind.field_voornaam_kind_value LIKE '$argument%') OR (node_data_field_voornaam_kind.field_achternaam_kind_value LIKE '$argument%') OR (node_data_field_voornaam_kind.field_postcode_value LIKE '$argument%')";

This one works,

and I need to add a filter that filters it to the nodetype 'klant'


Broseph’s picture

First of all thanks for the easy to follow instructions but I'm having a few issues.

function views_views_pre_execute(&$view) {
   if($view->name=="hud_downloads") {
     $view->build_info['query']="SELECT node.nid AS nid, node.title AS node_title, SUM(pubdlcnt.count) AS pubdlcnt_count FROM node node LEFT JOIN pubdlcnt pubdlcnt ON node.nid = pubdlcnt.nid  WHERE (node.type in ('huds')) AND (node.status <> 0) GROUP BY node.nid ORDER BY pubdlcnt_count DESC";

I've added this at the bottom of my views.module and everything displays as expected but I can no longer sort the values dynamically. Any idea what could be causing this? Thanks.

ari-meetai’s picture

iceous’s picture

WHERE (node.type in ('huds'))
replace huds with %s.

ealtman’s picture


bpwt’s picture

Thank you very much, it saved me.

ealtman’s picture

The "OR" selection works quite well when added this way, but the columns on the resulting table cannot be sorted. Even though it looks like the javascript is executing, the display is not being rewritten. Any ideas?

My query is to build a user-based view of book-parents and non-book nodes:
SELECT node.nid AS nid,
node.title AS node_title,
node.type AS node_type,
node.status AS node_status, AS users_name,
users.uid AS users_uid,
node.changed AS node_changed,
book_menu_links.depth AS book_menu_links_depth
FROM node node
LEFT JOIN book book ON node.nid = book.nid
LEFT JOIN menu_links book_menu_links ON book.mlid = book_menu_links.mlid
INNER JOIN users users ON node.uid = users.uid
WHERE ((book_menu_links.depth = '1') OR (book_menu_links.depth IS NULL)) AND (users.uid = ***CURRENT_USER***)

Krait’s picture

Here is some working code that reads the PHP global variable $_GET and through helper functions modifies the views query using methods from

Not the cleanest, but it is 4 AM and I finally managed to get this working..
Using $count and $query->set_where_group() to manually set the where claus groups, was the only way I could get the OR in the query.

function example_views_query_alter(&$view, &$query) {
    $query->add_field('field_data_field_color', 'field_color_tid');
    $changes = example_query(example_get_params($_GET));
    $sql = "SELECT tid FROM {taxonomy_term_data} WHERE name = :name;";
    $count = 0;
    foreach ($changes as $param => $values) {
        foreach ($values as $value) {
            foreach ($value as $actual => $op) {
                if ($param == 'color') {
                    $field = 'field_color_tid';
                    switch ($actual) {
                        case 'red':
                        case 'blue':
                        case 'brown':
                            if ($count >= 2) {
                                $query->set_where_group('OR', $param);
                            $actual = db_query($sql, array(':name' => $actual))->fetchField();
                } else {
                    $field = 'title';
                if (!empty($actual)) {
                    $query->add_where($param, $field, $actual, $op);

The case statement in there is not needed, but I do more with it in my code.

magda_’s picture

Excellent! Thank you, helped a lot.

I'd like to add an INNER JOIN to my query, but have almost no idea how to do it in drupal 7.
Something to do with $query->add_relationship ($alias, $join, $base, $link_point)? But I can't figure out what exactly $link_point or $join should stand for.

awm’s picture

just wondering if you figured out how to add an inner join with Hook_views_query_alter in Drupal 7?

awm’s picture


Mixologic’s picture

Good lord, *do not* take sql query parameters from $_GET. Your opening yourself up to SQL injection.

Abhijeet Sandil’s picture


monti’s picture

I have implemented this nice idea in a custom module. It's a 4-way join query using node, content_type____ and two data tables. computed_field is used to bind query dataset fields to view through: "echo $data->field_in_dataset;"

Everything looks as expected under admin account, but the view is empty for any other user.
This was very surprising to me. The content type has no restrictions, the "data" tables are readable to the world, the query itself does not need to check permission level.

Any clue here would be helpful.
Cheers !

monti’s picture

I came back to this much later and solved with a different approach -

(a) created MySQL views ("create view ... as ..." ) that together perform the required complex (eventually a 5-way join) query.
(b) adopted the final step query (view) as a "table" through data module interface.
(c) created a Views2 view on the MySQL, and voila ...

Sorry for the confusing usage of the term "view". This is how the're called.

Cheers !

Peters196’s picture

Firstly, I posted a message earlier today saying that I had a problem that views_views_pre_execute was not being called. Somehow that message got posted in the middle of this thread rather than the end!
Since then I have made a bit of progress. Maybe because of a buggy installation of the Notifications module (the subject of another post) or the way I was debugging (using PHP Print statements) I was being misled!
I have since discovered (by trial and error) the debug() function which takes a single string parameter as an argument, then displays that string in a green status box together with the flie name and line number where it was called - extremely useful, but sadly no documentation about it!

Anyway, the views_views_pre_execute() function is now being called, and, if I comment out SQL query string, the page displays normally, but if I define the SQL statement (which I have tested in SQL Workbench) I get a totally blank screen!

My requirement is slightly different from the others quoted earlier. I have a custom content type which has an added datestamp field. What I want the view to do is to display the nodes of this type in date order according to this datestamp (NOT the posting date!) and to only display those nodes which have a date after today's date.

Although the documentation on views suggests that the sorting and filtering that views can do is very powerful (and it would appear so from the videos I have seen of the Drupal 6 version) it appears that with the Drupal 7 version you can do things with the content and comment fields but nothing else! Which is why I have resorted to this custom code.

As I said, I have written a SQL query to do what I want. What I am not sure about is this - obviously the query result will be sent to the display section of views, but does this query need to be of a specific format? What fields should it include? Should the fields have specific names? Should the fields be in a specific order? Are there any other requirements that the query should meet?
I hope someone can help.

Peters196’s picture

I don't know whether it was a problem with my installation, my versions of Drupal and views, maybe I just didn't look closely enough at what I already had, or maybe I thought that what I wanted to do was outside the normal scope of views which required either advanced settings or hard coding!
Having re-installed everything with the latest versions and looking more closely I found that in the Sort option I not only had the standard content fields but every other field I had created, including the event date. So, by sorting on the event date in ascending order I had the events listed in chronological order. Furthermore, by adding a filter criterion based on the event date to include only those dates greater than or equal to Today I removed all past events from the view - everything I needed without a single line of PHP!
Views is truly a powerful bit of sotware!

voj’s picture

Modifiying the whole module would be a bad idea... I suggest that you create a new module that uses the query.
OR better yet, create a new views filter for that.

here's what I found

joseph.chambers’s picture

I suggest doing

function mymodule_views_pre_execute(&$view) {

   if($view->name=="My_view_name") {
     echo $view->build_info['query'];

This will allow you to see what the query looks like before it has been ran through db_rewrite_sql, modify the SQL from there and you should get even complex statements running. I am running UNIONS etc in one of mine and it works.

NeoPrince’s picture

I have tried something similar. But $view->build_info['query'] doesn't return a thing. The following is what I am trying.

function project_extras_views_pre_execute(&$view)
  if( ($view->name == 'my_view') &&  ($view->current_display == 'page_3') )

drupal_set_message($view->name );                                  /* #1: Cross checking */
drupal_set_message('Query:' . $view->build_info['query']); /* #2:  Expecting the query text*/

#1 returns the result, #2 doesn't. I have no idea why. Can someone help me please?

Countzero’s picture

As of this version, $view->build_info['query'] doesn't contain SQL anymore, but a SelectQuery Object.

So, if you ouput it via drupal_set_message, for example, the __toString magical method won't be called on the object (because the "text" PHP context is not triggered), and you won't see SQL but a dumped object :

    dsm($view->build_info['query']); // Will dump an object
    print $view->build_info['query']; // Will print an SQL statement

To have a nice debug output in the message area of your site :


As documented on

Hope it may avoid some hassle to some.

owenblacker’s picture

We've just had to do precisely this because Drupal simply can't cope with 200k rows coming back into a View (I note there was a ticket against 6.x-dev at #559424: Pagination using SQL scripts leads to heavy load that was "closed (won't fix)", which was particularly unhelpful).

We haven't sorted out the pagination or limiting on the script yet, but just optimising the query being sent to mySQL has halved the execution time. So we created a module, hard-coding our rôle IDs, to get those extra few milliseconds out of it. Rôle ID 5 is the rôle meaning that users are able to view profiles and use this piece of functionality; rôles 6, 8, 9 are administrative users that should not show up in our Friend Finder functionality.

function modulename_views_pre_execute(&$view) {
    if ($view->name  =="viewname") {
        global $user;
        $userid = $user->uid;

        $subquery = db_select('users_roles', 'squr');
        $subquery->where('squr.uid = u.uid');
        $subquery->where('squr.rid IN (6,8,9)');

        $custom_query = db_select('users', 'u');
        $custom_query->join('users_roles', 'ur', 'u.uid = ur.uid AND ur.rid = 5');
        $custom_query->addField('u', 'uid');
        // Three more addExpression lines here, copied directly
        // from what Devel showed us was the original query
        $custom_query->where('u.status = 1');
        $custom_query->condition('u.uid', $userid, '!=');
        //$custom_query->range(5, 10);    // Testing LIMIT and OFFSET

        //drupal_set_message($userid.' into '.$custom_query->__toString());
        $view->build_info['query'] = $custom_query;

I hope someone finds this useful!

mroscar’s picture

Thank you very much

fvictoire’s picture

It seems this code doesn't work for Views 7.
When I excute this code I have an internal server error. In the log, I can read "Call to a member function addMetaData() on a non_object in ... views/plugins/ on line 1438.

function execute(&$view) {
    $external = FALSE; // Whether this query will run against an external database.
    $query = $view->build_info['query'];
    $count_query = $view->build_info['count_query'];

    $query->addMetaData('view', $view);
    $count_query->addMetaData('view', $view);

    if (empty($this->options['disable_sql_rewrite'])) {

So $view->build_info['query'] is an object.

Has Anybody tried to do the same thing on views 7 ?

Thanks very much.

mikgreen’s picture

Yes, to my surprise it actually does work on Drupal 7.

Have a look at this example:

The trick is to use this syntax:

$query = &$view->build_info['query']; 

You can see the original are resulting query using this:


dreg’s picture

I'm having the same issue, any luck resolving this?

EDIT: The example from mikgreen is totally working! My query was just wrong, once I corrected it I was able to override succesfully.