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:

custom_views module

Downloads: 602

Size: 1.45 kB

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:

custom_views module

Downloads: 602

Size: 1.45 kB