The cure to duplicate nodes in a View when using multi-value fields
I only wanted the node to display once in the view. I edited my view display values. I set Distinct to Yes and checked "Group multiple values" on the field's configuration form. But these did nothing to suppress the duplicates. Knowing a thing or two about MySQL, I can see why Views is returning three rows – the join requires this.
Still, there should be some way to force the removal of duplicate node IDs after the query runs.
Many Others Looking for a Solution
There are countless issues filed for this problem with many declarations of a fix only to have those good feelings dashed when somebody re-opened the issue with someone saying it doesn't work for them:- Duplicate nodes in view when field with multiple values in field set
- Event repeats in Views when date has multiple dates
- Mutliple felds return duplicate rows in Views
- Views integration : handle 'multiple' fields
What I needed was the ability to manipulate the results after the query and before any rendering.
Enter hook_views_pre_render()
I called my friend and colleague Ben Di Maggio over at Digital Loom to discuss the issue. After a little groaning we started to go through the Views 2 API which is available from the Views project page.I immediately saw hook_views_pre_render() and I was over-joyed. I threw that function name into Google and found a blog post comment that got me 90% there.
I created a module with that single function override. I interrogated the $view->results object to be sure I was only acting on the "events" module and then write a loop to build a new results array with unique node IDs.
Views Remove Duplicates
I named my module 'viewsremoveduplicates' and so you will see that reference throughout.First create a directory named 'viewsremoveduplicates' in your sites/all/modules/ folder. You will then add two files. The first is the viewsremoveduplicates.info file:
name = Views Remove Duplicates description = Removes duplicate nodes. Requires editing the module file to identify the views you want to affect. package = "Views" core = 6.x dependencies[] = views
Then create the module file named viewsremoveduplicates.module
<?php
function viewsremoveduplicates_views_pre_render(&$view)
{
$used_nids = array();
if ($view->name == 'events')
{
if ($view->current_display == 'page_2')
{
foreach ($view->result as $row)
{
if (!in_array($row->nid, $used_nids))
{
$new_view_result[] = $row;
$used_nids[] = $row->nid;
}
}
$view->result = $new_view_result;
}
}
}
I enabled the module and that did it.
You will see that I am sure that the view name is "event". You will need to modify this code to match then name of your view. Unfortunately the $view object doesn't have the display name, otherwise, I would also narrow it down to a specific display. But, since we can't do that, be sure that you want to apply this code to all displays in the view. Otherwise, create a new view with the display(s) that you want to have modified by this module. The override is also limited to a specific display within the view, "page_2". You can change this to the ID of whatever display you are trying to override or remove the "if" statement completely to have affect the whole view.
Updates
Limit Configuration Field Adversely Affected, 2009-04-21
I've discovered that this solution doesn't play well with the "limit" configuration option in Views. The limit is applied during the query and this modification happens after the query and so we will always be in a position of reducing the list of nodes by the same amount as multiple dates.For example, if we want to limit a list of events to four and one of the events has three dates and the others have one, then we will only see two events in the list – one for the three-date event and one for single-date event.
This is a classic "cart before the horse" issue and I don't see how this can be resolved with my overall approach since the limit is being applied on the SQL and this code is reducing the number of nodes from there. If you have any suggestions, please share them in the comments.
Pager Adversely Affected, 2009-04-23
Some more fallout in applying this technique - the pager is affected as well. If you have a pager set to appear for any View that has over 20 items and you have multiple dates applied to events that total more than 20, you will get fewer dates appearing than 20.Still looking for the right answer.
PHP Errors Fixed, 2009-05-05
On the same day I discovered PHP errors in my logs, "blue muse" posted a comment noting the same. The good news is that I already had a fix for the problem by the time blue muse made the comment.I have updated the code for the module (above). I have removed the $query parameter so now there is only one. The $query variable was never used in my code, so no need for it anyway.
I also added an array declaration for $used_nids at the top of the function to avoid the error generated when the the loop in it's first cycle and the variable has not bee set yet.
Added Code To Override Just One Display, 2009-5-20
I discovered that I can in fact only override one display and I have altered the code above accordingly.
Comments
Group by
I think if you take this and modify your query with a "GROUP BY node.nid" before the query is executed, your problems will be fixed.
I'm working on this now, so sadly I can't give you the code yet. =(
Thanks for the try
Thanks for the attempt here. I installed it as you described, but it threw a bunch of errors about the 2nd argument and also the array.
It seems like a simple logical thing, but looks like it's a lot more complicated once you really get into it. From what I've read is it's a known issue, but noone wants to (or is able) to fix it.
We're on the same page
Thanks for the feedback. As it turns out, I discovered and fixed the issues moments before you made your comment. See the "PHP Errors Fixed" section above.
Thanks for this! Just what I
Thanks for this! Just what I needed. Although, I did need to remove the "&$query" from the function parameters. I think maybe Views doesn't accept that argument.
Ugh
Sorry about that, my update marked "2009-05-05" said that the "&$query" parameter wasn't needed and I *thought* I had removed it from the sample code, but did not. Glad you find it useful.
I also need to update the entry to correct myself in another area --- it *is* possible to only act on specific displays and not the whole view.
Thanks for this !
I was looking for a solution to this issue and found your module : thanks for this !
I have a question though : shouldn't this be submitted as a patch for the views module (once the "limit" and "paging" issues are figured out) ? I mean we shouldn't have to use an additionnal module to remove duplicates for multiple values...
Anyway, I have posted this in the french Drupal forums with due credits and link : http://drupalfr.org/node/6745
Yes, I would certainly hope
Yes, I would certainly hope it would be added as a patch to the Views module. When I have some more time, I would like to look into figuring our how to alter the result count that affects the number of nodes in a list and the pagination.
Alternative with no pager issues
I tried this method and it worked great but the pager issue bothered me. After examining the view object it seems this hook gets called too late in the process. At this stage the amount of returns is already set so if the pager is set to 20, we're subtracting the duplicate rows which leaves us with fewer than 20. I dug around a bit and found hook_views_query_alter which seems to do the trick. My nodes are trips, I don't mind if the node title is repeated but I wanted to get rid of duplicate dates. I'm no coding pro but this seems to be working for me.
function viewsremoveduplicates_views_query_alter(&$view, &$query)
{
if ($view->name == 'trip_search')
{
if ($view->current_display == 'page_1')
{
$query->groupby[0] = 'xmltrips_price_offers_begin_date';
}
}
}
Looks like there might be a
Looks like there might be a better fix this. I haven't had a chance to check it out, but will soon.
http://drupal.org/node/302498#new
A real/full/good solution
credit: http://drupal.org/node/302498#new
I'm using rc-5 and still get this problem with group multiple values checked, but i've found a work around for this - using an argument to get the nids of the nodes you're after you can prevent nodes appearing many times for each of their multiple dates.
* Add an argument of type 'Node: Nid'
* Select 'Provide default argument'
* Select 'PHP Code'
* Use code similar to mine below:
* Make sure you check 'Allow multiple terms per argument.'
* Set up the view with the fields you want
* Under Sort Criteria, if you want to sort by the date field you must set the 'Delta' to 1 if you don't want to see multiple values.
<?php
// Get future events
// counter
$i = 0;
// Get todays date as mySQL datetime
$today = date('c');
$result = db_query("SELECT DISTINCT n.nid FROM {node} n
INNER JOIN {content_field_event_date} d
ON n.nid = d.nid
WHERE d.field_event_date_value2 >= '%s'", $today);
while ($node = db_fetch_object($result)) {
if ($i > 0){ $nids .= ',';}
$nids .= $node->nid;
$i++;
}
return $nids;
?>
the CCK date table in my example is content_field_event_date - this needs to be changed to your CCK date table
the date field in my example is field_event_date_value2 - this needs to be changed to the name of your date field
This will return a comma separated list of nids to views.
Doing this seems to give exactly the same results as using a date filter with greater than or equal to, but eliminates multiple nodes appearing. You can obviously change the sql statement to have less than, or anything else.
slow performance
This code does work and does what is expected to remove duplicate nodes, however, it can be slow if there are many repeating events. A better way is to modify the query itself to only return distinct rows, rather than return all rows and have to go through them one by one in order to filter out the duplicates. This post is a good starting point -
http://drupal.org/node/409808
The idea is to override the hook hook_views_pre_execute(&$views) and intercept the query and modify it, perhaps by adding a group by nid clause to only return unique nodes.
Solved my problem
This solved a problem I was having using the "distinct" feature in views. The "distinct" feature was actually removing the duplicate nodes for me as it should - but I wanted it to always display the first occurrence of the node based on my sort order. I couldn't get this to happen using "distinct". This module displays the first occurrence of my node as I wanted. Thank you so much!
thanks!
Thanks a lot! This totally solved my problem. Weird problem by the way, writing a module for it feels like overkill (and it can be done otherwise) but thanks, it works!
great
After doing a research paper help for my cousin I need to fix up my problem to my freelance project. Until I found this blog. It is really helpful and it fixed my problem. Great!
a good starting point
Thank you it works for me. And i've customized it for my needs.
It's the first module I use that modify views result programatically. I think it will be very useful for me as a departure point for more customization.
Thanks
This was a great
This was a great solution--thank you!!!
Because I was getting duplicates from the data using the relationship, I modified it to look for duplicates in the data subset. Very useful . . . .
Thank you for the solution,
Thank you for the solution, it helped me to solve my problem. Just one hint - you can implement this hook in your theme template.php file
Post new comment