Getting duplicates on member directory

[dhorter]

Hello all,

  I'm using CiviCRM in a website and am using the Profile function to have a searchable member directory.  I'm using a smart group for a custom field (included in directory field) to base the profile off of.  It's working fine, but I'm getting duplicate entries for everyone who has signed up. 

  The site member search is located at http://www.manageanimalpain.com/ivapm/index.php?option=com_civicrm&view=Profiles&layout=search&Itemid=71 if you would like to take a look.  Just hit search, as there the site is still in beta and there are not a lot of registered participants. 

Any thoughts on this?

Thanks in advance!
Dennis

[jawnski]

I'm getting duplicates in CiviCRM member directory, too. I tried to de-dupe with no luck. Sometimes the member will appear twice and then if I enter another unique member the duplicate will disappear. I'm sure this is something simple. But I haven't been able to find the answer so far.

[dhorter]

I haven't had much luck on troubleshooting this either.  I should also add that I'm using Joomla 1.5.15 and CiviCRM 3.0.  I am working on a few things with my test site, but no luck so far.

Thanks!
Dennis

[chillin411]

I'll add to the list of duplicates - D6.16 and C3.0.4 for now.  It seems to work just fine if I view the smartgroup through Contacts -> Manage Groups, but when I view a profile, no dice.  I'm going to try upgrading Civi first, and then see if there's anything in the database that's throwing it off, but the smartgroup cache table has the correct amount of members.

[Dave Greenberg]

One issue which has caused duplicate result rows in the past is when contacts have > 1 primary email address or phone number. This should NOT happen since the UI is supposed to ensure only one email and one phone are marked primary, but we have seen folks w/ this issue in the past.

I recall someone posted a SQL query to check for this issue on the forums - so you might want to do a search for that. Both tables have an is_primary column, should be only one marked true for each DISTINCT contact_id.

[Kiran Jagtap]

Yes, we already fixed to make sure we should has to have only single primary record ( address, email, phone, IM, openId ) from UI as well as at BAO code level.

But there are some edge cases where folks might already have multiple primary records in old civicrm version database, also we did fixed this edge in v3.1.3 upgrade process.
( http://issues.civicrm.org/jira/browse/CRM-5856 )

I'm sure civicrm v3.1.3 and onward there will be no multiple primary records.

So you might want to upgrade civicrm to latest stable release ( v3.1.3 )

kiran

[chillin411]

No dice.  Upgraded to 3.1.3 using the upgrade guide, and still having duplication issues.

I'm going to keep tracking it, as I'm feeling like it has to be something with the profile itself.  The smartgroup listing is pulling correctly otherwise.

If I can find the solution, I'll submit a patch to CRM-5856 and post here.

[chillin411]

I've gotten it down to the query, and it seems like the DISTINCT isn't applying how I'd expect it to - or I'm missing something obvious.  Probably the latter, given that it's Friday.  I'm still plugging away at it.

[chillin411]

Okay, I have a solution, but it doesn't feel like the right solution.

Changing line 829 in CRM/Contact/BAO/Query.php from:

Code: [Select]

$locationTypeJoin[$tName] = " ( `$tName`.location_type_id = $ltName.id )";
to:

Code: [Select]

if ($tableName != 'civicrm_email') {
  $locationTypeJoin[$tName] = " ( `$tName`.location_type_id = $ltName.id )";
}
fixes the duplicate issues, but I feel like I'm breaking something somewhere else that I don't know about.  Are there any built-in unit tests I could try running?  This is my first time really digging into CiviCRM, so I don't really know my way around that well, but I have that creepy feeling that I need that LEFT JOIN ON for something else that's not evident to me.

If this is okay, then I'll roll together a patch with this and submit it.

[chillin411]

I know I'm just replying to myself over and over - but perhaps someone in the future ends up finding this thread, as I'm not sure I've found the solution yet.

It seems like just commenting out line 829 completely fixes the problem.  Does anyone know what I could be potentially breaking by commenting this out?

[Deepak Srivastava]

Hey chillin411,

Could you send us your db dump (compressed) for investigation to deepak at webaccess.co.in. That 'll help us recreate and fix the problem.

[CiviTeacher.com]

I *was* having the exact same issue.  

An example here:
http://www.nscss.org/civicrm/profile?force=1&gid=6&search=0

The record, Steven Currie was showing up twice.

As you can see, the member, Steven Currie only has one primary phone and one primary email address, but the location type was different... home vs. work.  When I changed both Primaries to the "Home" location, the duplication stopped.

I think it's reasonable that the a person could have a primary home email, but a primary work phone, without duplications showing up, but this is something for the CiviCRM core team to discuss...  at least we have our reasons now.

[Dave Greenberg]

Stuart - If you can recreate this in the current version on demo, please file a bug report on the issue tracker. I agree that having your Primary email and Primary phone at 2 separate locations should not result in duplicate rows in the a profile listing. (No promises as to how quickly this can be fixed as I suspect there's some complex sql join issues causing this behavior.)

[florenceit]

Hi there..
just confirming I found this thread having the same problem and this fixed it for me. so far this will not be an issue for me but I think , yes its VERY likely that we'll have contacts with home and work fields, almost unavoidable at some point.

I *was* having the exact same issue.  

An example here:
http://www.nscss.org/civicrm/profile?force=1&gid=6&search=0

The record, Steven Currie was showing up twice.

As you can see, the member, Steven Currie only has one primary phone and one primary email address, but the location type was different... home vs. work.  When I changed both Primaries to the "Home" location, the duplication stopped.

[CiviTeacher.com]

I have replicated this bug on the Demo site here:
http://drupal.demo.civicrm.org/civicrm/profile/?gid=9&force=1&search=1&reset=1

Filed as a bug in JIRA here:
http://issues.civicrm.org/jira/browse/CRM-6082