I have a recent posts view (with various fields) and recent comment view. Is it possible to combine these sorted by date? Is there any way to combine views like this - either through themeing, custom query, etc?

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dawehner’s picture

You could use panels and display the views among themselves

cerup’s picture

Thanks, panels will only let me display both independently which isn't what I'm looking for.

I'm trying to interweave multiple views so it'll be like:

-John posted XX 12 hours ago
-Jim posted XX 15 hours ago
-John commented XX 18 hours ago
-jim favorited XX 23 hours ago
-marrie posted XX 22 hours ago
-Jim posted XX 23 hours ago
-John favorited XX 23 hours ago
-John commented XX 23 hours ago
-phil commented XX 23 hours ago
-phil commented XX 23 hours ago

etc.

Basically trying to make a custom activity feed using views, but it would require combining various views (since I haven't been able to create just 1 view that can query and format everything).

I know there's an Activity module, but it's much more than I'm looking for and would require a lot of changes to display the fields the way I'd like.

Since each view would have a date field, I'm just wondering if it's possible to use that field so that the views can be merged (shown in a list) determined by date.

EvanDonovan’s picture

Maybe you could merge into a single table that was populated on cron, and then write the code in a custom module to handle display of that table.

Subscribing :)

cerup’s picture

Would it be possible to do this by using UNION? Does views have a UNION type option?

(SELECT * from example) UNION (SELECT * from example2) ORDER BY date

or maybe using the results of two views in a new view?

$result = mysql_query("SELECT * FROM example ORDER BY date");
$result2 = mysql_query("SELECT * FROM example2 ORDER BY date");
$row = mysql_fetch_assoc($result);
$row2 = mysql_fetch_assoc($result2);
//merge the two results??

fed into a new view?

cerup’s picture

Title: Combine multiple views - is this possible? » Combine or merge multiple views/queries into a single view - is this possible?
jrwyse’s picture

Title: Combine or merge multiple views/queries into a single view - is this possible? » Yes Please

I have one view that lists all nodes of three different types. I have one view that lists all comments. Both are sorted by date and are Style: Grid. Would like to merge the two (or even three or four views) into one view and sort everything by date, creating a Facebook Like News Feed. This has to be possible :) but I am wrecking my brain on the SQL post above.

cerup’s picture

Title: Yes Please » Combine or merge multiple views/queries into a single view - is this possible?

Make sure not to change the title of an issue, unless it clarifies what the issue is about. ;) I've put the title back because 'yes please' doesn't describe the issue.

With that said, I figured out a way and am in the process of writing up a tutorial. I'll post here within the next week with the tutorial link.

jrwyse’s picture

cerup, thanks so much... I apologize for changing the title... I'm less than a month into learning Drupal and new to the site :)

EvanDonovan’s picture

Title: Combine or merge multiple views/queries into a single view - is this possible? » How to: Combine or merge multiple views/queries into a single view
Version: 6.x-2.8 » 6.x-2.10

@cerup: Glad to hear you're writing up a tutorial. I am changing the issue title in preparation for that :)

Also, I love that you fixed your own issue :)

cerup’s picture

No problem jrwyse - we were all 'new' at some point :)

Here's the tutorial. It looks a little harder (more advanced) than it may appear - http://sethsandler.com/software/drupal-6-creating-activity-stream-views-...

I have to clean it up a bit, but hopefully it's a start for some people that are intermediate - advanced.

Bilmar’s picture

Thanks for the great tutorial - I learned a lot!
If you've written any other tutorials please let me know =)

EvanDonovan’s picture

Title: How to: Combine or merge multiple views/queries into a single view » How to: Combine or merge multiple views/queries into a single view (using hook_views_pre_execute)
Status: Active » Fixed

cerup, your tutorial is immensely helpful! I've wanted to know how to use the pre_execute hook for a long time, but never looked into it before. Now I have a fairly good idea of how it works. Thanks, and marking as fixed.

Once set to fixed, the issue will stay open for two weeks, so others can see it.

cerup’s picture

Glad to hear it's helpful. It's my first drupal tutorial so I wasn't sure how it'd turn out ;)

Khanfoor’s picture

Thank you very much. It's a great tutorial !!
But please help me. My custom view module returns only first ten nodes from query result !!!!
What's the solution of this strange issue? May be the code in custom_views_views_pre_execute function needs some additions or I should implement another hook?!!!

cerup’s picture

Hi Khanfoor,

I've updated Step 4 of the tutorial. You need to add:

$view->build_info['count_query']=$view->build_info['query'];

After the query (you can check the tutorial again). The count_query sets the views pager (count) so you have to set it to the new query or else it'll only count the original objects. Let me know if that fixes it.

Khanfoor’s picture

FileSize
3.12 KB

Thank you very much !! It works fine now.

But now I have another problem. My custom query needs to use an argument. I have a content type called "Organization Unit" that has a node reference field to itself to represent hierarchy of my organization. There is also an Employee content type that has a node reference field to an Organization Unit. The purpose of my view is to show all employees works under specific organization unit in any level so it is consisted of a union of three queries (because the maximum number of levels in my organization is 3): The first one retrieve all employees assigned immediately to the unit (by one join operation and the argument is node id of the employee's unit), the second retrieves all employees assigned to sub units (using two joins and the argument here is the nid of the parent unit), and the third retrieves all employees assigned to sub-sub units (using three joins and the argument of this query is nid of grandparent unit). So the three queries use different arguments with the same value (there is a single value but it is used in different place of each query).
I solved this problem using "arg(1)" in the right places in my custom complex query without adding any argument through views ui. But now I can't benifit from views arguments capabilities: for example I can't give my view a title like "Employees in %1 unit". Also I use a path of "node/%/employees" for my view and because it has no argument I can't validate the nid to make sure it is nid of an Organization Unit node and the menu tab appears in all nodes !!
Sorry, I know it is a complicated issue but I hope you can help me. You can see my query in the attatched file to better understand my problem.

cerup’s picture

Try replacing arg(1) for '%d' (with the quotes).

Then, before the custom query, put:

$view->build_info['query_args'][2] = $view->build_info['query_args'][1] = $view->build_info['query_args'][0];

This will create 3 arguments (first one created automatically, but you need to create the next 2 to pass to the second and third union). This should pass each argument to the 3 %d places.

Let me know if that works.

Khanfoor’s picture

Thank you very much. I've done it in a different way using views ui and it appears the same.
I added three relationships to my view representing employee's unit, parent unit and grandparent unit respectively, then I added three arguments to the view: the first one uses the grandparent relationship, the second uses parent relationship and the third uses the first relationship. for last two argument I chose 'provide default argument' and select 'node id from url' and it works !!

Your tutorial is great, thank you again.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

dafeder’s picture

Just wanted thank cerup here for his great tutorial as well. This is the best way I've seen to do a views UNION query in Drupal 6. A lot of ways to tweak this for one's own needs, I've posted some suggestions over in the tutorial's comment section.

donquixote’s picture

Another possibility is to programmatically fetch from different views and merge the results in PHP.

Jeffrey04’s picture

thanks for the tutorial :D

nortont’s picture

Great information but it does not seem to work for D7

I tried changing the following

In custom_views.module

function custom_views_views_api() {
return array('api' => 3.0);
}

In custom_views.info

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

The module installs but in views it does not seem to detect it.

Jerome F’s picture

@dereine in #1 :
What do you mean by display the views among themselves ?
Is it possible to merge 2 views in panels ?
Merlinofchaos suggested the same thing in : http://drupal.org/node/915010#comment-3460798
But I don't know how to do that.

liquidcms’s picture

historical insight..

in D5 there was a module called Views Union which would have done this easily.
in D6 there was a module call Views Or, which claimed to replace Views Union and with some complex setup would do this (or the complex write your own module approach suggested here)
in D7 i think we have nothing other than possibly a variant of the custom code suggested here

nkschaefer’s picture

In response to #25, Views 3 in Drupal 7 did incorporate the ability to create groups of filters that can use either "and" or "or" - just like the Views Or module did in Drupal 6. To do that, click the little drop down arrow next to "Filters" and choose "and/or."

There doesn't seem to be an easy way, however, to union results together (or create similar "and/or" groups in the Fields section of Views). I understand why, though - it is a pretty specific use case and it would be very hard to write a proper SQL query that would know how to sort/display items, when each item has a different field being sorted on and/or displayed.

One issue to watch, though, is this one: http://drupal.org/node/1266036. It looks like they're working on (and close to finishing) Views integration for the Entity API module. I'm not totally sure about this, but I'd think that could mean Views fields could become more abstracted (for instance, you could choose "Entity: title" or "Entity: ID" rather than the specific "Taxonomy term: name" or "Node: NID"). This, combined with the and/or filters implemented in Views 3, could lead to much more flexible views that could combine multiple types of results together into one list.

Kristen Pol’s picture

Version: 6.x-2.10 » 7.x-3.x-dev
Status: Closed (fixed) » Active

This was addressed for Drupal 6. I'm wondering if anyone has an update for doing the same thing for Drupal 7?

Also, it would be great if there was a generic way to handle this without copy/pasting views query code into your module... e.g. using the views API to get the query and then manipulating as variables.

Thanks,
Kristen

rongok’s picture

Anyone has a solution for Drupal 7?

mitchell’s picture

You could use Views Rules with a Rule that appends one list to another.

I don't have much more info at hand to add to this, because I'm working on project applications (like #1101938: Views Unionize) now, but I'd be happy to check back into this later.

pedrodias10’s picture

Why don't you just filter the content type and put those two (e.g. post, comment), you just have to first put the data filter and after the content type to sort the correct way.

bianchi’s picture

can I use it with drupal 7 ??
thanks

aronne’s picture

In Drupal 7 you can solve this problem following these steps implementing hook_views_pre_execute():

/**
 * Implements hook_views_pre_execute()
 */
function modulename_views_pre_execute(&$view){
	if($view->name == 'my_view'){
		//extracts my nodes
		$query2 = db_select('node', 'node');

		//created and nid are the same fields used by my_view query
		$query2->addField('node', 'created', 'node_created');  
		$query2->addField('node', 'nid', 'nid');

		//set some conditions if necessary
		$query2->condition('node.type', 'event', '=')
			   ->condition('node.status', 1, '=');
		
		//union custom query with my_view default query
		$query2->union($view->build_info['query'], 'UNION ALL');
		
		//in order to prevent Cardinality violation errors i have to make a "total" query
		$total_query = db_select($query2,'total')->fields('total');

		//update views query and count_query with my new query.
		$view->build_info['query'] = $total_query;
		$view->build_info['count_query'] = $view->build_info['query']; //count_query is necessary for the pager
	}
}

I hope this could help! It worked really well for me!

Bell@

theingizaw’s picture

@aronne

Thanks for your post. It's great help to me.
I tried for node views and aggregate views.

I can output only node id, title and post date.

Whey I output body field , I got error: Fatal error: Class name must be a valid object or a string in /mysqldata/nextweb/includes/common.inc on line 7749.

Any solution?

Thanks

Chipie’s picture

Can I use the method in #32 to combine two different entity types (e.g. comments and nodes)? I want to create a view with all nodes and comments sorted by post date.

Sherif Darwish’s picture

@aronne

Thanks for this, you really helped me

i will post my modified version as well to show one more bit of information about doing the union with 2 different types for examples users and nodes and including additional type static field that shows which type (node or user) is this

/**
 * Implements hook_views_pre_execute()
 */
function YOUR_MODULE_NAME_views_pre_execute(&$view){
    if($view->name == 'YOUR_VIEW_NAME'){
        //extracts my nodes
        $query2 = db_select('node', 'node');
		$query2->leftJoin('location_instance', 'location_instance', 'node.vid = location_instance.vid');
		$query2->leftJoin('location', 'location', 'location_instance.lid  = location.lid');

        //created and nid are the same fields used by my_view query
        $query2->addField('node', 'title', 'users_name');  
		$query2->addField('node', 'nid', 'uid');  
		$query2->addField('location', 'latitude', 'location_latitude');  
		$query2->addField('location', 'longitude', 'location_longitude');  
		$query2->addExpression(':the_group_type', 'Type', array(':the_group_type' => 'Group'));

        //set some conditions if necessary
        $query2->condition('node.type', 'group', '=')
               ->condition('node.status', 1, '=');
        
		$query1 = $view->build_info['query'];
		$query1 ->addExpression(':the_user_type', 'Type', array(':the_user_type' => 'User'));
		
        //union custom query with my_view default query
        $query2->union($query1, 'UNION ALL');

		
        //in order to prevent Cardinality violation errors i have to make a "total" query
        $total_query = db_select($query2,'total')->fields('total');
		
        //update views query and count_query with my new query.
        $view->build_info['query'] = $total_query;
        $view->build_info['count_query'] = $view->build_info['query']; //count_query is necessary for the pager
    }
}
Chipie’s picture

@Sherif Darwish

Thanks. Your sample did help me a lot.

I have still a problem. I want to create an union with nodes and comments. When I add the body of a comment in the view, the field field_data_comment_body_comment_entity_type is added to query.

How can I add the body of node in query2?

Any ideas?

Regards,
Stéphane

saptha’s picture

I merged two views for my website and I can see the merged output.
Its wonderful but I got another error:
Warning: date_timezone_set() expects parameter 1 to be DateTime, boolean given in format_date() - (line 2014 of website/includes/common.inc)

Please advise how to get rid of this warning

Thanks
Saptha

TechNikh’s picture

Issue summary: View changes

You can use Views extra handlers module to union SQL queries of two different views/displays. https://www.drupal.org/project/views_extra_handlers
screenshot of union settings

vulfox’s picture

@TechNikh

Do you know how to use the mentioned Union with https://www.drupal.org/project/views_extra_handlers ?

I tried with test install:

- opened the default "tracker" for editing
- added field to the bottom of the fields list: "Global: Query Alter"
- at the "Union" setting:

View id: recent_comments

VIew display id: page

Got error:
SQLSTATE[HY000]: General error: 1221 Incorrect usage of UNION and ORDER BY

any tips?

vulfox’s picture

Okay from http://www.w3schools.com/sql/sql_union.asp

"Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order."

So this helps a bit, but still no... no errors, but empty rows.

facuchaer’s picture

@vulfox what the query is saying is that you are using order by before union... order by should only be placed in the last query, don't know anything about that module, but know about that error, and order by's with unions must be on the last query

somersoft’s picture

FileSize
9.81 KB

Perhaps reading through #1145076-39: UNION queries don't support ORDER BY clauses will provide a solution along with the attached patch file. This

  1. Adds a sort by text field when defining which view to combine with.
  2. Passes the current view arguments to the associated view.

It also has the fix from #2614002-3: wrong results.

ugintl’s picture

I am trying to create an autocomplete view of content and users, but I do not know how to combine them. Anybody?

Yazzbe’s picture

Hi Ugintl,

What I ended up doing is copy the data needed for the autocomplete filter on save in a separate long text field (with Rules), and use that long text field as the source for my autocomplete filter.

robert_t_taylor’s picture

#32 was the key to my understanding how to accomplish something similar in D8. Thanks @aronne!

MustangGB’s picture

Status: Active » Fixed

Seems like this has been answered.
Also I want to mention Views Field View may be able to help as well.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.