Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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?
Comment | File | Size | Author |
---|---|---|---|
#42 | views_extra_handlers.patch | 9.81 KB | somersoft |
#16 | custom_query.txt | 3.12 KB | Khanfoor |
Comments
Comment #1
dawehnerYou could use panels and display the views among themselves
Comment #2
cerup CreditAttribution: cerup commentedThanks, 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.
Comment #3
EvanDonovan CreditAttribution: EvanDonovan commentedMaybe 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 :)
Comment #4
cerup CreditAttribution: cerup commentedWould it be possible to do this by using UNION? Does views have a UNION type option?
or maybe using the results of two views in a new view?
fed into a new view?
Comment #5
cerup CreditAttribution: cerup commentedComment #6
jrwyse CreditAttribution: jrwyse commentedI 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.
Comment #7
cerup CreditAttribution: cerup commentedMake 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.
Comment #8
jrwyse CreditAttribution: jrwyse commentedcerup, thanks so much... I apologize for changing the title... I'm less than a month into learning Drupal and new to the site :)
Comment #9
EvanDonovan CreditAttribution: EvanDonovan commented@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 :)
Comment #10
cerup CreditAttribution: cerup commentedNo 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.
Comment #11
Bilmar CreditAttribution: Bilmar commentedThanks for the great tutorial - I learned a lot!
If you've written any other tutorials please let me know =)
Comment #12
EvanDonovan CreditAttribution: EvanDonovan commentedcerup, 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.
Comment #13
cerup CreditAttribution: cerup commentedGlad to hear it's helpful. It's my first drupal tutorial so I wasn't sure how it'd turn out ;)
Comment #14
Khanfoor CreditAttribution: Khanfoor commentedThank 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?!!!
Comment #15
cerup CreditAttribution: cerup commentedHi Khanfoor,
I've updated Step 4 of the tutorial. You need to add:
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.
Comment #16
Khanfoor CreditAttribution: Khanfoor commentedThank 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.
Comment #17
cerup CreditAttribution: cerup commentedTry replacing arg(1) for '%d' (with the quotes).
Then, before the custom query, put:
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.
Comment #18
Khanfoor CreditAttribution: Khanfoor commentedThank 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.
Comment #20
dafederJust 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.
Comment #21
donquixote CreditAttribution: donquixote commentedAnother possibility is to programmatically fetch from different views and merge the results in PHP.
Comment #22
Jeffrey04 CreditAttribution: Jeffrey04 commentedthanks for the tutorial :D
Comment #23
nortont CreditAttribution: nortont commentedGreat information but it does not seem to work for D7
I tried changing the following
In custom_views.module
In custom_views.info
The module installs but in views it does not seem to detect it.
Comment #24
Jerome F CreditAttribution: Jerome F commented@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.
Comment #25
liquidcms CreditAttribution: liquidcms commentedhistorical 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
Comment #26
nkschaefer CreditAttribution: nkschaefer commentedIn 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.
Comment #27
Kristen PolThis 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
Comment #28
rongok CreditAttribution: rongok commentedAnyone has a solution for Drupal 7?
Comment #29
mitchell CreditAttribution: mitchell commentedYou 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.
Comment #30
pedrodias10 CreditAttribution: pedrodias10 commentedWhy 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.
Comment #31
bianchi CreditAttribution: bianchi commentedcan I use it with drupal 7 ??
thanks
Comment #32
aronne CreditAttribution: aronne commentedIn Drupal 7 you can solve this problem following these steps implementing hook_views_pre_execute():
I hope this could help! It worked really well for me!
Bell@
Comment #33
theingizaw CreditAttribution: theingizaw commented@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
Comment #34
Chipie CreditAttribution: Chipie commentedCan 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.
Comment #35
Sherif Darwish CreditAttribution: Sherif Darwish commented@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
Comment #36
Chipie CreditAttribution: Chipie commented@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
Comment #37
saptha CreditAttribution: saptha commentedI 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
Comment #38
TechNikh CreditAttribution: TechNikh commentedYou can use Views extra handlers module to union SQL queries of two different views/displays. https://www.drupal.org/project/views_extra_handlers
Comment #39
vulfox CreditAttribution: vulfox commented@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?
Comment #40
vulfox CreditAttribution: vulfox commentedOkay 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.
Comment #41
facuchaer CreditAttribution: facuchaer as a volunteer commented@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
Comment #42
somersoft CreditAttribution: somersoft commentedPerhaps reading through #1145076-39: UNION queries don't support ORDER BY clauses will provide a solution along with the attached patch file. This
It also has the fix from #2614002-3: wrong results.
Comment #43
ugintl CreditAttribution: ugintl commentedI am trying to create an autocomplete view of content and users, but I do not know how to combine them. Anybody?
Comment #44
Yazzbe CreditAttribution: Yazzbe commentedHi 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.
Comment #45
robert_t_taylor CreditAttribution: robert_t_taylor commented#32 was the key to my understanding how to accomplish something similar in D8. Thanks @aronne!
Comment #46
MustangGB CreditAttribution: MustangGB commentedSeems like this has been answered.
Also I want to mention Views Field View may be able to help as well.