DRUPAL VIEWS - Important points and Optimization

Drupal Views is an extremely useful Query building tool! Drupal Views brings with it a great amount of flexibility to the way existing content can be displayed on a particular website.

This article highlights some important points to remember before creating a Drupal View. The concepts are explained with respect to a News paper site as example.



The Basic elements of a Drupal Views include:

• Base Tables - Node tables, Comment tables, user tables, etc.
• Fields - information that gets displayed
  
• Filters - the criteria based on which the information is querried
  
• Sort criteria - the criteria based on which the content is sorted and displayed.
  
• Arguments - variable filters that can be changed dynamically when the view is constructed.
  
• 'Relationship - integrating with custom tables
  

POINTS to remember:

1. Reuse Views and use FILTERS as ARGUMENTS - Using Filters as Arguments lets the view display contextual content. In a Newspaper site the same view can be used multiple times for various taxonomies, taxonomy being an argument. The BLOB size of the VIEWS TABLE is very large, reducing the number of views helps in maintainability and in Performance by reducing the View table size. A couple of use cases of Arguments in Views for a Newspaper site:
   
1. In the home page of a Newspaper a Single view with multiple sub channels - a single view can be used, the result obtained by passing the corresponding sub channel taxonomy can be displayed sequentially.
2. In an Article page, the article tags can be passed as arguments to the same view to display related content based on that particular article.
2. Typical filters to include are 
1. NODE type == Published, 
2. Node Article Type, 
3. Node Created date.
3. Always define what should happen if a view returns a 'Null value'. Typically define a message/page  it should take you to and display a specific message.
   
4. While defining Arguments, define what should happen if there were no arguments passed to the view. This could be 'All values' or a Page not found, etc.
5. UNDERSTANDING the query associated with a VIEW: In Drupal 6 you can Preview a VIEW while you are still building it. In Drupal it's so easy to construct a view and most of the time take it for granted. Below are some key points that will help you understand the View Query and give some pointers as to what to look for.
1. Pager Query - In this case there are 2 Queries that run - one to count the total number of records and build the pager, second the Query to obtain the first set of records for that page.
   For a block/ page view with a count or limit there is only one Query.
   
2. Based on the fields displayed, the filters selected and the sort criteria defined, the JOINS in the Query will be constructed. Some examples:
1. If the filter is CCK field there will be a INNERJOIN to the corresponding CCK table
   
   
   
   

   SELECT node.nid AS nid,node.title AS node_title,

    node_data_field_article_date.field_article_date_value AS node_data_field_article_date_field_article_date_value,

    node.sticky AS node_sticky,   node_weight.sticky AS node_weight_sticky

    FROM node node 

    LEFT JOIN term_node term_node ON node.vid = term_node.vid

    LEFT JOIN content_field_article_date node_data_field_article_date ON node.vid = node_data_field_article_date.vid

    LEFT JOIN node node_weight ON node.nid = node_weight.nid

    WHERE (node.type = 'article') AND (node.status <> 0) AND (term_node.tid = 4)

    ORDER BY node_data_field_article_date_field_article_date_value DESC, node_sticky ASC, node_weight_sticky DESC

   
   
   
   
2. For a SORT criteria as most commented the INNERJOIN to the comment stats realted table
   
   
   
   

   SELECT node.nid AS nid, node.title AS node_title,

    node_comment_statistics.comment_count AS    node_comment_statistics_comment_count

    FROM node node 

    INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid

    WHERE (node.type in ('article')) AND (node_comment_statistics.comment_count > 0) AND (node.status <> 0)

    ORDER BY node_comment_statistics_comment_count DESC
   

   
   
   
   
6. OPTIMIZING the Queries:
   
1. Make sure all the fields that are used for SORTING are indexed, especially if it is a custom table.
2. If there is an explicit limit to the VIEW please define the same.
   
3. If a part of the VIEW cache-able , then split the VIEW into Cache-able element that is stored in a cache table and Dynamic parts as a separate view.
4. Use VIEWS' inbuilt time based caching mechanisms, or write custom cache code using Drupal's cache get and Cache set functions.
5. The Query optimization is based on which tables are Large. Typically in Drupal some of the Large tables are the Node related tables, Comment related tables, Taxonomy related table, Files related tables, User related tables. This also depends on the site and it's purpose.

EXAMPLE of a slow Query - Option 2:

In the case of a large Node table - Option 1 will take less time to execute as the 'Where' clause filters the IDs in the Node table first and then filters based on the tid, which is stored in another table.

VIEW Query Option1:

SELECT node.nid AS nid,

   node.title AS node_title,

   node_data_field_article_date.field_article_date_value AS node_data_field_article_date_field_article_date_value,

   node.sticky AS node_sticky,

   node_weight.sticky AS node_weight_sticky

 FROM node node 

 LEFT JOIN term_node term_node ON node.vid = term_node.vid

 LEFT JOIN content_field_article_date node_data_field_article_date ON node.vid = node_data_field_article_date.vid

 LEFT JOIN node node_weight ON node.nid = node_weight.nid

 WHERE (node.type = 'article') AND (node.status <> 0) AND (term_node.tid = 4)

   ORDER BY node_data_field_article_date_field_article_date_value DESC, node_sticky ASC, node_weight_sticky DESC

VIEW Query Option 2:

SELECT node.nid AS nid,

   node.title AS node_title,

   node_data_field_article_date.field_article_date_value AS node_data_field_article_date_field_article_date_value,

   node.sticky AS node_sticky,

   node_weight.sticky AS node_weight_sticky

 FROM node node 

 LEFT JOIN term_node term_node ON node.vid = term_node.vid

 LEFT JOIN content_field_article_date node_data_field_article_date ON node.vid = node_data_field_article_date.vid

 LEFT JOIN node node_weight ON node.nid = node_weight.nid

 WHERE (term_node.tid = 4) AND (node.type = 'article') AND (node.status <> 0) 

   ORDER BY node_data_field_article_date_field_article_date_value DESC, node_sticky ASC, node_weight_sticky DESC

Other Resources on Views

VIEWS Handbook page: http://drupal.org/node/109604
VIEWS useful links: http://www.lullabot.com/articles/hacking-views-part-1-basic-concepts
VIEWS 2 - Building Views with Fivestar and Voting API modules: http://www.lullabot.com/videocast/building-views-fivestar-and-votingapi