0

So I've been trying to use the db_select() in a view hook (in this case, _views_pre_execute()) to overwrite the query that exists in a given view. I've followed instructions and gotten what I think should be correct, but would really rather use raw SQL. According to Lullabot, it's perfectly OK to use db_query() with raw SQL if your query doesn't have any dynamic components, which mine doesn't.

Problem is, I can't seem to get anything to happen. I've never seen a complete example anywhere of using one of these view hooks, so I'm not sure what else I need to do.

Every (partial) sample I've seen has something like, "// Perform operations on $record->title, etc. here." inside the foreach loop that processes the query result. Do I need to use that to manually write out the HTML for each field I want to display? I've tried that, and although the drupal_set_message() line is printing out, the foreach results are not. Do I need to hide all the fields from display in the Views UI?

I just can't seem to get the results of the query to print to the screen (so I really don't even know if the query is returning any results, although when run directly in phpMyAdmin it does return the desired records.) What IS printing is the view as configured in the UI, even though (I thought) using this hook was supposed to overwrite that.

Can anyone help me out? I've spent a week trying to figure out how to get this working, and I'm at the end of my rope.

Here are two versions of my function code; one using db_query and the other using db_select. I'd be happy if I could get either to actually print results to the screen.

db_select

function group_speaker_carousel_views_pre_execute(&$view){
    if ($view->name == 'speakers_carousel')  {
      drupal_set_message("view is {$view->name}");

      $query = db_select('node', 'n');

      $query->leftJoin('field_data_field_speaker', 'sp', 'n.nid = sp.field_speaker_target_id');
      $query->leftJoin('field_data_field_job_title', 'job', 'n.nid = job.entity_id'); 
      $query->leftJoin('field_data_field_company', 'company', 'n.nid = company.entity_id');    

      $query->addField('n', 'title', 'node_title');
      $query->addField('job', 'field_job_title_value', 'field_job_title_value');
      $query->addField('company', 'field_company_value', 'field_company_value');
      $query->condition('n.status', '1', '=');
      $query->condition('n.type', 'speaker', '=');
      $query->orderBy('n.title', 'DESC');
      $query->range(0,1);

      $result = $query->execute(); 


      foreach($result as $record) {
        echo "<strong>" . $record->field_company_value . "</strong>";
      }

    }
}

db_query

function group_speaker_carousel_views_pre_execute(&$view){
    if ($view->name == 'speakers_carousel')  {
      drupal_set_message("view is {$view->name}");
      $result = db_query("SELECT n.title AS node_title, job.field_job_title_value AS field_job_title_value, company.field_company_value AS field_company_value
                         FROM {node} n
LEFT OUTER JOIN {field_data_field_speaker} sp ON n.nid = sp.field_speaker_target_id
LEFT OUTER JOIN {field_data_field_job_title} job ON n.nid = job.entity_id
LEFT OUTER JOIN {field_data_field_company company} ON n.nid = company.entity_id
WHERE (((n.status =  ':status') AND (n.type = ':type')))
ORDER BY node_title DESC",

array(':status' => '1', ':type' => 'speaker'));


      foreach($result as $record) {
        echo "<strong>" . $record->field_company_value . "</strong>";
      }

    }
}
  • Why are you using a view in the first place, when you seem to replace it with a custom query? Doesn't look like you're building a query that you couldn't do in Views? Instead of using echo/print, use dpm($record) from devel.module to debug what your query returns. If don't see anything, it's possible that there are no results for that query... – Berdir Jul 9 '12 at 19:17
  • We're using a view because we wanted to use the JCarousel view format. I had thought that we could replace the query and still have it output using that. As far as building a query that we can't do in Views, the query that I have in my OP is not the actual final query; it's a much-simplified version that I was using to test the technique. The actual query has a bunch of group_concats and other things that we don't have access to via the Views UI. I'll try the devel trick and see if anything prints out. – EmmyS Jul 9 '12 at 19:34
  • I guess you should look into hook_views_query_alter() instead. Note that while it might work, it's meant to alter and not replace the query completely. See drupal.stackexchange.com/questions/3950/… for example, or drupal.stackexchange.com/questions/15378/… (6.x) – Berdir Jul 9 '12 at 19:51

Your Answer

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

Browse other questions tagged or ask your own question.