Drupal – Customize the Views Query

I want to create a taxonomy view which shows the term name and the term image. For those terms without term image, i want to filter out them. Unfortunately there is no term image filter in the view setting. One work around is overriding the view query by implementing the hook_views_pre_execute(&$view) in a custom module. Before you construct the new SQL, you should set all your desire settings in the view editing page first. Then you can create a new SQL base on that view query.

So in the custom module, add the following function.

/**
 * Override the query SQL in a view
 *
 * 1. Check the view name by drupal_set_message($view->name);
 * 2. Get the original SQL by drupal_set_message($view->build_info['query']);
 * 3. Modify the original SQL as of your preference
 * 4. Set the new SQL to $view->build_info['query']
 */
function custom_views_pre_execute(&$view) {
  /* 1. Check the view name */
  //drupal_set_message($view->name);

  /* 2. Get the original SQL */
  //drupal_set_message($view->build_info['query']);

  /* 3. Modify the original SQL as of your preference */
  // I just change the LEFT JOIN to RIGHT JOIN =P

  /* 4. Set the new SQL to $view->build_info['query'] */
  if ($view->name == "view_name") {
    $view->build_info['query'] = "SELECT term_data.tid AS tid, term_image.tid AS term_image_tid, term_data.name AS term_data_name, term_data.vid AS term_data_vid FROM {term_data} term_data RIGHT JOIN {term_image} term_image ON term_data.tid = term_image.tid WHERE (term_data.vid in ('%s')) AND (term_data.language in ('%s')) ORDER BY term_data_name ASC ";
  }
}

 

Since you have override the query SQL, any new changes to the view through the view editing page will no longer valid. This is why we should set all desire settings in the view editing page before overriding it.

Done =)

Reference: implementing custom sql query for views / filters

About these ads

4 thoughts on “Drupal – Customize the Views Query”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s