2

I have a view which I could not get to function the way I wanted through the Views interface, so I implemented hook_views_pre_execute in my module (following the steps in http://drupal.org/node/409808).

My rewritten query (see below) adds a conditional with a subquery to the WHERE clause of the original query. I've tested it in phpMyAdmin, and it works perfectly. But, when testing the View in the Drupal, it acts like the NOT IN conditional does not exist. (ie. I get 3 results instead of 2).

One thing I found while searching for answers was someone saying that db_rewrite_sql (which is a function called by Views when it executes the query) is unable to handle "complex queries". Could this be my problem?

Does anyone have any idea why this is behaving strangely?

Here's the hook_views_pre_execute code in my module, with the modified query.

function vmr_test_views_pre_execute(&$view) {

  if ($view->name == 'collected_machines_by_site' && $view->current_display == 'panel_pane_2') {
    $site_id = $view->args[0];
    $date = $view->args[1];
    $query = "SELECT node.nid AS nid, node.title AS node_title 
FROM node node  
LEFT JOIN content_type_machine_financials node_data_field_machine_reference_url ON node.vid = node_data_field_machine_reference_url.vid 
INNER JOIN node node_node_data_field_machine_reference_url ON node_data_field_machine_reference_url.field_machine_reference_url_nid = node_node_data_field_machine_reference_url.nid 
LEFT JOIN content_type_machine node_node_data_field_machine_reference_url_node_data_field_current_location ON node_node_data_field_machine_reference_url.vid = node_node_data_field_machine_reference_url_node_data_field_current_location.vid 
INNER JOIN node node_node_data_field_current_location ON node_node_data_field_machine_reference_url_node_data_field_current_location.field_current_location_nid = node_node_data_field_current_location.nid 
LEFT JOIN content_type_site node_node_data_field_current_location_node_data_field_location_id ON node_node_data_field_current_location.vid = node_node_data_field_current_location_node_data_field_location_id.vid 
WHERE (node.type in ('machine_financials')) AND (node_node_data_field_current_location_node_data_field_location_id.field_location_id_value = '" . $site_id . "' ) 
AND node.nid NOT IN (SELECT DISTINCT node_data_field_mach_ref_url.field_mach_ref_url_nid AS rev_event_nid FROM node node LEFT JOIN content_type_revenue_event node_data_field_mach_ref_url ON node.vid = node_data_field_mach_ref_url.vid WHERE (node.type in ('revenue_event')) AND ((DATE_FORMAT(field_transaction_date_value, '%Y-%m-%d') <= '" . $date . "' AND DATE_FORMAT(field_transaction_date_value, '%Y-%m-%d') >= '" . $date . "')))";
    $view->build_info['query'] = $query;
    dpm($view);
  }
}

EDIT: I've been troubleshooting, and came across something that is just way too puzzling. I used the following code to see if my subquery was working in Drupal.

$subquery = "SELECT DISTINCT node_data_field_mach_ref_url.field_mach_ref_url_nid AS rev_event_nid FROM node node LEFT JOIN content_type_revenue_event node_data_field_mach_ref_url ON node.vid = node_data_field_mach_ref_url.vid WHERE (node.type in ('revenue_event')) AND ((DATE_FORMAT(field_transaction_date_value, '%Y-%m-%d') <= '" . $date . "' AND DATE_FORMAT(field_transaction_date_value, '%Y-%m-%d') >= '" . $date . "'));";
    $result = db_query($subquery);
    for ($i = 0; $i<3; $i++) {
      dpm(db_fetch_array($result));
    }

And I'm getting NOTHING. I used a dpm() statement to print the subquery with the dates already substituted, as seen by the program, then copy-pasted that into phpMyAdmin and it worked PERFECTLY! So somewhere along the line, whatever Drupal is doing is causing the above subquery to just not work. Any ideas at all would be helpful at this point. I'm thinking about bypassing Drupal's database abstraction entirely just so I can get the real results for this subquery.

EDIT 2: I solved the problem by just using mysql php functions directly, instead of going through Drupal's db_* functions. I'm going to leave this open in case anyone can explain why the subquery was returning nothing when using Drupal's database abstraction functions.

1

In the dull axe arena, it looks to me that db_rewrite_sql() does it's "magic" by looking for WHERE, so, try changing WHERE to where, mysql shouldn't care, and this MIGHT bypass the re-writing and allow your code to run as is. Of course, this defeats drupal's permissions checking.

  • Tried changing both my WHERE's to lower-case, but it was no good. Still getting that extra result. Thanks for the suggestion, though! – Goluxas Dec 23 '11 at 14:35
  • I guess the next step is to put some debug dsm()s or equivs in there to see what it IS turning your query into – Jimajamma Dec 23 '11 at 17:47
0

You need to set pager query as well:

$view->build_info['count_query'] = $query->countQuery();

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.