24

At some point I found the need to modify an SQL query generated by Views, in the end I overrode views_pre_execute and changed the query for that specific view.

This feels like an ugly hack to me and I wonder if there is a more elegant and maintainable way to do that. Ideal would be a way that would allow me to directly modify the query from the Views UI.

  • 1
    It depends on how you wanted to modify that query. What exactly were you trying to accomplish? – Jason Smith Mar 2 '11 at 21:37
  • @Jason I posted the question to SO at that time: stackoverflow.com/questions/3147916/… But that problem is solved now, I'm just looking for an elegant way to modify any Views query if I have to. – Mad Scientist Mar 2 '11 at 21:43
  • I'm not convinced that you can't accomplish what you were trying to do in the other thread just using views. That said, there's far more than one way to skin this cat. – Jason Smith Mar 2 '11 at 22:00
  • If any of the answers below are what you were looking for, you should accept one (click the check mark below the vote count) – Chaulky Jun 2 '11 at 2:25
  • hook_views_pre_execute may not be the most elegant, but it does have its place for complex query overrides (see Custom Views 3 queries in Drupal 7) – mrP Aug 27 '13 at 0:34
25
+50

You can also use hook_views_query_alter() to alter the query before it's run. I think this is similar to hook_views_pre_execute, but makes it easier to modify the query. You basically get access to each part of the query through a keyed array. I haven't found much official documentation, but there's a pretty good example of it at https://www.appnovation.com/blog/using-hook-views-query-alter. This is also the approach I had to use to fix a date bug in the Calendar module.

  • Will this work with Views-3 as well? – markdorison Mar 4 '11 at 20:57
  • @markdorison I believe so, but haven't confirmed that – Chaulky Mar 24 '11 at 13:12
  • 3
    I have confirmed that this works in Views-3. – markdorison Mar 28 '11 at 21:10
  • 1
    @Fabian you should accept this answer if it was useful for you, or comment on why it wasn't so we can make it better – Chaulky Jun 7 '11 at 22:30
  • Another example reference for Custom Views 3 queries in Drupal 7 using hook_views_pre_execute() in a simple custom module. – mrP May 23 '13 at 1:37
4

In general this depends on your use case.

If you want to have a field/filter/argument which should behave in a certain way, it is recommended to write a handler for it. See advanced help of views for more information.

If you want to change some parts of the query you can also use hook_views_query_alter(). The bad thing about hook_views_query_alter() is that you can't really reuse the code there.

This is the example code shown from the documentation. It gives an example of what the hook can do.

function mymodule_views_query_alter(&$view, &$query) {
  // (Example assuming a view with an exposed filter on node title.)
  // If the input for the title filter is a positive integer, filter against
  // node ID instead of node title.
  if ($view->name == 'my_view' && is_numeric($view->exposed_raw_input['title']) && $view->exposed_raw_input['title'] > 0) {
    // Traverse through the 'where' part of the query.
    foreach ($query->where as &$condition_group) {
      foreach ($condition_group['conditions'] as &$condition) {
        // If this is the part of the query filtering on title, chang the
        // condition to filter on node ID.
        if ($condition['field'] == 'node.title') {
          $condition = array(
            'field' => 'node.nid', 
            'value' => $view->exposed_raw_input['title'], 
            'operator' => '=',
          );
        }
      }
    }
  }
}
3

I have used the hook_views_query_alter() to alter a views mysql query. The following example is tested under Drupal 7 with 7.x-3.0, it adds a custom ORDER BY clause to the query:

 function MYTHEME_views_query_alter(&$view, &$query) {
   // check so it's the correct view
   if($view->name == 'product_view') {
     // set a custom 'ORDER BY' clause in the query
     $query->orderby[0] = array(
       'field' => 'SUBSTR(taxonomy_term_data_name,3,4)',
       'direction' => 'ASC'
     );
     $query->orderby[1] = array(
       'field' => 'SUBSTR(taxonomy_term_data_name,1,2)',
       'direction' => 'ASC'
     );
   }
 }
1

I don't know if you can directly change the sql, but you could write your own field handler and craft your own query.

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .