27,680 views 49 comments

Drupal 6 – Creating Activity Stream with Views Custom SQL Query: Merging multiple views – Part 1

If you’re using Drupal, you’re probably using views. If you’re using views, than you’ve probably wanted to build a view that involved combining content and found out it was hard or almost impossible through the views UI.

For example, if you’ve ever tried to build a single view of all content (posts, comments, flags, relationships, etc) into a single list, you’ve probably found that you can’t. However, you can easily build independent post views, comment views, flag views, and relationship views.  Since that’s the case, why can’t we merge all these views into a single view through the views UI? Well you can, and this article hopefully will give the basic information needed to start merging your views into more complex results.

After some themeing, our results will look something like the following which is a combination of comments, flags (sparks), and posts (various content types):

In this article, we’ll be building a basic activity stream to demonstrate the ability to

  1. create custom queries for views,
  2. merge our results,
  3. create our custom query module, and
  4. finally output through the views UI.

 

DOWNLOAD EXAMPLE MODULE:

Initial Multitouch Aesthetic Plans (0 downloads)

Introduction:

Before we begin, we have to know what we’re making. We’re going to be creating a list view (you can choose another type if you please) of posts and comments from our site ordered by date. Later, you can choose to add more activity into your view like flags (likes), relationships (who’s following whom), etc. but for now we’ll keep it simple since the same principle for 2 types of information also applies to 5 types.

Step 1: Making the query

To create our activity stream view, we’re essentially going to combine two individual views into a single query. We’re going to take advantage of the views UI preview in order to get our query (in case we don’t know too much about SQL queries).

    1. Create a new temporary view (we’re not actually going to save it) called recent_posts (or similar).
    2. Add fields for: user name, user picture, node title, node post date, and node type.
    3. Add a sort criteria for Node: poste date.
    4. Add a filter for Node: published.

Your view should look like the above

    1. In the views ‘live preview’, click the preview button.
    2. Scroll down to where the ‘query’ is shown and copy and paste this to a temporary text file on your desktop. We’re going to need this query code in a bit.

    1. Create a new temporary view (we’re not actually going to save it) called recent_comments (or similar).
    2. Add relationship for: Comment: node and Comment: User.
    3. Add fields for: user name, user picture, node title,  comment title, and comment post date (all fields related to their relationships).
    4. Add a sort criteria for Comment: poste date.
    5. Add a filter for Node: published.

  1. Scroll down to where the ‘query’ is shown and copy and paste this to the same file you created in step 6. We’re going to need this query code in a bit.

Step 2: Combine and merge the queries using UNION

We should now have 2 views queries copied to a text file that look something like:

//posts view
SELECT node.nid AS nid,
users.picture AS users_picture,
users.uid AS users_uid,
users.name AS users_name,
users.mail AS users_mail,
node.type AS node_type,
node.title AS node_title,
node.created AS node_created,
history_user.timestamp AS history_user_timestamp,
node.changed AS node_changed,
node_comment_statistics.last_comment_timestamp AS node_comment_statistics_last_comment_timestamp
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN history history_user ON node.nid = history_user.nid AND history_user.uid = ***CURRENT_USER***
INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE (node.status <> 0)
ORDER BY node_created DESC

//comments view
SELECT comments.cid AS cid,
users_comments.picture AS users_comments_picture,
users_comments.uid AS users_comments_uid,
users_comments.name AS users_comments_name,
users_comments.mail AS users_comments_mail,
comments.subject AS comments_subject,
comments.nid AS comments_nid,
node_comments.title AS node_comments_title,
node_comments.nid AS node_comments_nid,
comments.timestamp AS comments_timestamp
FROM comments comments
LEFT JOIN node node_comments ON comments.nid = node_comments.nid
LEFT JOIN users users_comments ON comments.uid = users_comments.uid
WHERE (node_comments.status <> 0)
ORDER BY comments_timestamp DESC

    1. Views adds a few things that we really won’t need. So we can remove all the bold parts above to clean up our queries. The results should look like:

//posts view
SELECT node.nid AS nid, //node id
users.picture AS users_picture, //picture
users.uid AS users_uid, //user id
users.name AS users_name, //user name
node.type AS node_type, // node type
node.title AS node_title, // node title
node.created AS node_created //post date
FROM node node
INNER JOIN users users ON node.uid = users.uid
WHERE (node.status <> 0)
ORDER BY node_created DESC

//comments view
SELECT comments.cid AS cid, //comment id
users_comments.picture AS users_comments_picture, // picture
users_comments.uid AS users_comments_uid, //user id
users_comments.name AS users_comments_name, //user name
node_comments.type AS node_type, //node type
comments.subject AS comments_subject, // comment title
comments.timestamp AS comments_timestamp //post date
FROM comments comments
LEFT JOIN node node_comments ON comments.nid = node_comments.nid
LEFT JOIN users users_comments ON comments.uid = users_comments.uid
WHERE (node_comments.status <> 0)
ORDER BY comments_timestamp DESC

    1. The result above are much cleaner. We’re going to merge these two queries by using UNION. UNION combines the results of multiple SELECTs. If you’re not familiar with UNION, i’d recommend reading this. In order to to create a union you have to have the same amount of SELECT fields. Luckily, when we cleaned up our view we were left with 7 SELECT fields (id, picture, user name, node type, title, post date). If you don’t have the same amount of fields in each query, you’ll need to create the same amount by modifying your queries or adding a ‘NULL’ select in the query that has less fields. To make a UNION query of the above is simple. Copy and cut the ORDER BY node_created DESC from the end of the first query and replace the ORDER BY comments_timestamp DESC from the end of the second query with it.  Then, add UNION‘ bewteen the two queries and you’re done! Results below:

//posts view
SELECT node.nid AS nid, //node id
users.picture AS users_picture, //picture
users.uid AS users_uid, //user id
users.name AS users_name, //user name
node.type AS node_type, // node type
node.title AS node_title, // node title
node.created AS node_created //post date
FROM node node
INNER JOIN users users ON node.uid = users.uid
WHERE (node.status <> 0)

UNION

//comments view
SELECT comments.cid AS cid, //comment id
users_comments.picture AS users_comments_picture, // picture
users_comments.uid AS users_comments_uid, //user id
users_comments.name AS users_comments_name, //user name
node_comments.type AS node_type, //node type
comments.subject AS comments_subject, // comment title
comments.timestamp AS comments_timestamp //post date
FROM comments comments
LEFT JOIN node node_comments ON comments.nid = node_comments.nid
LEFT JOIN users users_comments ON comments.uid = users_comments.uid
WHERE (node_comments.status <> 0)
ORDER BY node_created DESC

  1. This is going to merge our two views (posts and comments) and sort them by post date. Meaning, intstead of having 2 seperate views (one after the other), we now have a single view query that sorts both views (together) by node_created/comments_timestamp.  We just created our custom query! Now it’s time to prepare it to be fed into views 😀

Step 3: Creating the custom query module

Now that we have our custom query ready to feed into views, we have to create a module to feed it into the views UI.  Sound scary? It’s not – I’ll be giving you all the pieces below.

    1. Create a folder on your desktop called custom_views
    2. Create a text file on your desktop called custom_views.info
    3. Inside the file, paste:

; $Id$
name = Custom Views
description = Custom views query for this site.
core = 6.x

    1. Save this file.
    2. Create a text file on your desktop called custom_views.install
    3. Inside this file, paste:

<?php // $Id: custom_views.install

/**
* Implementation of hook_install()
*/
function custom_views_install() {
// Set the module weight so it can override other modules.
db_query(“UPDATE {system} SET weight = 99 WHERE name = ‘custom_views'”);
}

/**
* Implementation of hook_uninstall()
*/
function custom_views_uninstall() {
// Remove the module from the system table
db_query(“DELETE FROM {system} WHERE name = ‘custom_views'”);
}

    1. Save this file.
    2. Create a text file on your desktop called custom_views.module
    3. Inside this file, paste:

<?php // $Id: custom_views.module,v 1.272.2.1 20010/05/07 12:25:24 goba Exp $

function custom_views_views_pre_execute(&$view) {

if($view->name==”SiteActivity”) {//if we don’t do this it’ll alter ALL our view queries with this one!

$view->build_info[‘query’]=”//custom query goes here“;

$view->build_info[‘count_query’]=$view->build_info[‘query’];  // count_query determines the pager.  Do this so the right item count is returned.

}

}

/**
* This module is Views 2.0 enabled.
* Implementation of hook_views_api().
*/
function custom_views_views_api() {
return array(‘api’ => 2.0);
}

    1. Inside the custom_views_views_pre_execute function where it says ‘custom query goes here‘, paste the custom query we made in step 2 . Result below:

<?php // $Id: custom_views.module,v 1.272.2.1 20010/05/07 12:25:24 goba Exp $

function custom_views_views_pre_execute(&$view) {

if($view->name==”SiteActivity”) //if we don’t do this it’ll alter ALL our site’s view queries with this one!
{
$view->build_info[‘query’]=

SELECT node.nid AS nid, //node id
users.picture AS users_picture, //picture
users.uid AS users_uid, //user id
users.name AS users_name, //user name
node.type AS node_type, // node type
node.title AS node_title, // node title
node.created AS node_created //post date
FROM node node
INNER JOIN users users ON node.uid = users.uid
WHERE (node.status <> 0)

UNION

SELECT comments.cid AS cid, //comment id
users_comments.picture AS users_comments_picture, // picture
users_comments.uid AS users_comments_uid, //user id
users_comments.name AS users_comments_name, //user name
node_comments.type AS node_type, //node type
comments.subject AS comments_subject, // comment title
comments.timestamp AS comments_timestamp //post date
FROM comments comments
LEFT JOIN node node_comments ON comments.nid = node_comments.nid
LEFT JOIN users users_comments ON comments.uid = users_comments.uid
WHERE (node_comments.status <> 0)
ORDER BY node_created DESC”;

$view->build_info[‘count_query’]=$view->build_info[‘query’];  // count_query determines the pager.  Do this so the right item count is returned.}
}

/**
* This module is Views 2.0 enabled.
* Implementation of hook_views_api().
*/
function custom_views_views_api() {
return array(‘api’ => 2.0);
}

  1. Save this file. We just created our custom views module. 😀
  2. Now upload your custom_views module to your website host and enable it like normal.

Step 4: Output our activity stream through the views UI

Now that we have our new module, with our custom query added to our site, it’s time to create our final view.

    1. Create a new node view and call it ‘SiteActivity‘. We’re picking this name because that’s what we put in our module “if($view->name==”SiteActivity“)”. If you want to call it something else, you’ll need to change the name in the custom_views module too.
    2. Since the query we made had user picture, user name, node type, title, and post date, we’ll add fields for all of those.

  1. You can arrange these how you want. Preview your new view and see what it looks like! Hopefully it should be a combination of other views you created before, but now they’re combined into a single list!
  2. The original example in the beginning of this post goes a bit further (probably covered later in a part 2 of this tutorial). It uses views custom fields in order to take all the results and images based upon the content type and activity type (project, flag, comment). With a little creativity you can do the same.

Final Thoughts:

Most of the themeing parts of this tutorial will be saved for another time (part 2) since they deserve a separate part. In this example we covered how to combine multiple views queries and sorting based on a common part (post date). The method of creating a custom query and module to input in views should work with any custom query and not just UNIONs. So there is a lot of room for creativity. I hope you enjoyed this tutorial. I don’t claim to be an expert by any means and so please let me know if there are problems or insights that could improve upon this process. If there are any steps missing or clarification needed – let me know so I can make the necessary changes/additions.

DOWNLOAD EXAMPLE MODULE:

Initial Multitouch Aesthetic Plans (0 downloads)
Did you enjoy this post?

Comment Below , Follow Me on Twitter , Follow Me on Facebook , or Network with Me on Linkedin

  • Danny_Joris

    Hello,

    Thank you for this great tutorial. Unfortunately it isn’t working for me yet. Can you see what I could be doing wrong? http://drupal.org/node/950560#comment-3614600

    Tnx!

  • sam lowry

    Hi, thanks for the tutorial.
    I’m using this for displaying 2 different content types which are both kind of “update news” for a website. They have similar fields but I need them separate because 2 different groups of users are using it (and the results are styled in 2 different ways depending on the content type).
    My ORDER BY field is obviously the date. The SQL works fine, results are ordered the way I want, but i’ve got a strange issue which I think depends on Drupal: even if the date field is used correctly by the ORDER BY, the one taken from the second query isn’t shown!
    and if I invert the two selects, the one who wasn’t shown before now it’s there in all his magnificency and the other one (which was in the first SELECT and now it’s in the second one) is hidden.

    here is my query:

    SELECT node.nid AS nid,
    node.type AS node_type,
    node_data_field_avviso1_data1.field_avviso1_data1_value AS node_data_field_avviso1_data1_field_avviso1_data1_value,
    node.vid AS node_vid,
    node.title AS node_title,
    node_revisions.body AS node_revisions_body,
    node_revisions.format AS node_revisions_format
    FROM node node
    LEFT JOIN content_type_avviso1 node_data_field_avviso1_data1 ON node.vid = node_data_field_avviso1_data1.vid
    LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
    WHERE node.type in (‘avviso1’)

    UNION

    SELECT node.nid AS nid,
    node.type AS node_type,
    node_data_field_data2.field_data2_value AS node_data_field_data2_field_data2_value,
    node.vid AS node_vid,
    node.title AS node_title,
    node_revisions.body AS node_revisions_body,
    node_revisions.format AS node_revisions_format
    FROM node node
    LEFT JOIN content_type_avviso2 node_data_field_data2 ON node.vid = node_data_field_data2.vid
    LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
    WHERE node.type in (‘avviso2’)

    ORDER BY 3 ASC

    so, if I put the select for ‘avviso2’ after the UNION the field ‘field_data2_value’ isn’t shown (but IT IS ordered correctly), if I switch the two SELECTs the field ‘field_data2_value’ is shown but ‘field_avviso1_data1’ isn’t.
    Any suggestions?
    If I put the same query in my SQL manager I get the full results with all the fields I need.

  • Pingback: 100+ Drupal Views 使用指南资源 | 滴水穿石|Drupal中文化企业网站建设()

  • Pingback: Drupal Views tutorials and resources « ddrupal()

  • I am so excited to find this solution that uses a slight customization and relies on the existing Drupal modules to do the rest. It will be fewer worries during Drupal updates.

    My problem was that Views_Calc would not use the Relationship value when running the calculation. I had begun to read about making a custom module with all the perms, menus, blocks, pages, etc. Since I am a volunteer with our nature group and not a web designer, the task was going to use up more time than I had to give.

    Your method is a welcome short cut.

  • James

    Any chance you could show us how to do this for D7? Thanks!

    • I’ll have to see if I can. I actually haven’t moved to Drupal 7 yet and I’m sure some of this is quite different.

    • James

      Great stuff, looking forward to seeing it. Thanks

  • jodathegrey

    Hi, I just found this and tried using this method it – I want to blank out certain fields using SQL in a view dependent of field entries at a row level (use alternative contact details and hide original ones if flagged as such when an unregistered user views the list)  – the SQL works find in mySQL and I get the reformatted data rows I am looking for, but when I use the above approach I dont get any data when creating the view (preview link) or when I save the view and call it from a URL (its not a permission problem) any ideas? Thanks

  • aharown

    Pure gold! One minor issue: the long version of the comments query has node title in it but the shortened version has node type instead. I’m only part way through the process but thought I’d point that out…. Don’t know much about queries and Views but I’m assuming node type is the correct line there.

  • aharown

    Problems: can’t seem to enable the custom_views.module (get server errors when enable and save) …. In looking for reasons why, found that there were several curly quotes in the file where there should have been straight. At least, these are always straight in other module files. Corrected that, but still can’t enable the module. 
    Can anyone suggest other places to look for errors?
     

    • Yeah, for some reason my blog is converting the quotes incorrectly. What type of error(s) are you getting?

    • aharown

      In Chrome it was just a white screen and a really generic server error. I did eventually get it to work. Unfortunately, I’m not sure why. Basically did the whole process over again and, just in case it mattered, made the final View before enabling the custom views module. That worked fine.
      Maybe I just missed a quote mark or some other character the first time around.

    • aharown

      Error is just a 500 internal server error…. “unexpected condition was encountered”
      Tried to make a second module with a diff. view and I’m getting the error again. 

  • Dunx

    Very useful post, the method still works on D6, but the detail needs a little tweak: you need to make sure the “AS” names match between the two parts of the SELECT.

    So, as an example the “AS nid” in the first SELECT, needs to match the “AS nid” in the second SELECT. It was “AS cid” and that doesn’t work.

    SELECT node.nid AS nid, //node id
    [snip]
    FROM node node
    [snip]
    UNION
    SELECT comments.cid AS nid, //comment id
    [snip]
    FROM comments comments
    [snip]

  • Pingback: Drupal Views tutorials()

  • Pingback: artisan parfumeur()

  • Pingback: read this article()

-->