Views group ID: displaying same contacts multiple times with relationships.

[andersiversen]

Hi

[EDIT: changed the title to reflect the real problem, as it has to do with "group id" as well]

I'm using civicrm 4.4.4.
I've a bunch of schools in civicrm and lots of students with the relationship "student at (contact A), school for (contact B).
A few of the students is in a group called "Contact persons".

I've been trying to make a simple view with a table consisting of two columns: column one lists the school's display name and column two lists the contact person's display name.
To do that I've added two relationships for the view:
1 CiviCRM Contacts: CiviCRM Relationship (starting from Contact B)  - this is the school
2 (CiviCRM Relationship (starting from contact B)) CiviCRM Relationships: Contact ID A - this is the student, and it uses the 1st relationship.

The fields:
Display name (school - doesn't use any relationship)
Display name  (student - uses relationship 2)

Filters:
(CiviCRM Contact A) CiviCRM Groups: Group ID (= 64) (the "contact persons"-group)
(CiviCRM Contact A) CiviCRM Groups: Contact Status (= Added)

This doesn't work as expected. The problem is that there are a lot of duplicate entries (same school same contact on several rows - I've tried added contact Id's to check that it wasn't duplicate contacts in civicrm - it is in fact the exact same contacts displayed multiple times).
I tried this way first, as I want to display schools even though they don't have a related student in the group "contact persons". I think what might be happening is that the view displays a row for each school, but for the schools with no contact person it just displays the latest school and contact person found, and not the actual school name with an empty cell in the contact person column.

Doing it the other way around works as expected:
Relationships:
CiviCRM Contacts: CiviCRM Relationship (starting from contact A)
(CiviCRM Relationship (starting from contact A)) CiviCRM Relationships: Contact ID B

The fields:
Display name (school - uses relationship 2)
Display name  (student - doesn't use any relationship)

The filters:
CiviCRM Groups: Group ID (= 64)
CiviCRM Groups: Contact Status (= Added)

This view shows no duplicates. But as far as I can figure out, doing it this way, will not let me display schools with no related students in the "contact persons" group.

Here is the view query for the first view with all the duplicates:

Code: [Select]

SELECT civicrm_contact.id AS id, civicrm_contact.display_name AS civicrm_contact_display_name, civicrm_contact_civicrm_relationship.id AS civicrm_contact_civicrm_relationship_id, civicrm_contact_civicrm_relationship.display_name AS civicrm_contact_civicrm_relationship_display_name
FROM
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_relationship} civicrm_relationship_civicrm_contact ON civicrm_contact.id = civicrm_relationship_civicrm_contact.contact_id_b AND (civicrm_relationship_civicrm_contact.relationship_type_id = '11' AND civicrm_relationship_civicrm_contact.is_active = '1')
LEFT JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship_civicrm_contact.contact_id_a = civicrm_contact_civicrm_relationship.id
LEFT JOIN {civicrm_group_contact} civicrm_contact_civicrm_relationship__civicrm_group_contact ON civicrm_contact_civicrm_relationship.id = civicrm_contact_civicrm_relationship__civicrm_group_contact.contact_id
LEFT JOIN {civicrm_group_contact} civicrm_contact_civicrm_relationship_civicrm_contact_civicrm_relationship__civicrm_group_contact ON civicrm_contact_civicrm_relationship.id = civicrm_contact_civicrm_relationship_civicrm_contact_civicrm_relationship__civicrm_group_contact.contact_id
LEFT JOIN {civicrm_group} civicrm_contact_civicrm_relationship__civicrm_group ON civicrm_contact_civicrm_relationship__civicrm_group_contact.group_id = civicrm_contact_civicrm_relationship__civicrm_group.id
WHERE (( (civicrm_contact.contact_sub_type IN  ('School')) AND (civicrm_contact_civicrm_relationship.contact_sub_type IN  ('Student')) AND (civicrm_contact_civicrm_relationship__civicrm_group.id = '64') AND (civicrm_contact_civicrm_relationship__civicrm_group_contact.status IN  ('Added')) AND (civicrm_contact_civicrm_relationship.is_deleted = '0') AND (civicrm_contact.is_deleted = '0') ))
ORDER BY civicrm_contact_display_name ASC

Here's the second view with no duplicates (i've added phone and email columns for the student in this view)

Code: [Select]

SELECT civicrm_contact.id AS id, civicrm_contact_civicrm_relationship.display_name AS civicrm_contact_civicrm_relationship_display_name, civicrm_contact_civicrm_relationship.id AS civicrm_contact_civicrm_relationship_id, civicrm_contact.display_name AS civicrm_contact_display_name, civicrm_phone.phone AS civicrm_phone_phone, civicrm_email.email AS civicrm_email_email
FROM
{civicrm_contact} civicrm_contact
LEFT JOIN {civicrm_relationship} civicrm_relationship_civicrm_contact ON civicrm_contact.id = civicrm_relationship_civicrm_contact.contact_id_a AND (civicrm_relationship_civicrm_contact.relationship_type_id = '11' AND civicrm_relationship_civicrm_contact.is_active = '1')
LEFT JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship_civicrm_contact.contact_id_b = civicrm_contact_civicrm_relationship.id
LEFT JOIN {civicrm_group_contact} civicrm_group_contact ON civicrm_contact.id = civicrm_group_contact.contact_id
LEFT JOIN {civicrm_group} civicrm_group ON civicrm_group_contact.group_id = civicrm_group.id
LEFT JOIN {civicrm_phone} civicrm_phone ON civicrm_contact.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = '1'
LEFT JOIN {civicrm_email} civicrm_email ON civicrm_contact.id = civicrm_email.contact_id AND civicrm_email.is_primary = '1'
WHERE (( (civicrm_contact_civicrm_relationship.contact_sub_type IN  ('School')) AND (civicrm_contact_civicrm_relationship.is_deleted = '0') AND (civicrm_contact.contact_sub_type IN  ('Student')) AND (civicrm_contact.is_deleted = '0') AND (civicrm_group.id = '64') AND (civicrm_group_contact.status IN  ('Added')) ))
ORDER BY civicrm_contact_civicrm_relationship_display_name ASC

This might be a views issue, or me not being able to figure out how to do it proberly.
All help is apreciated.

[andersiversen]

Trying the problematic view query in mysql also gives duplicates, but removing this line resolves it:

Code: [Select]

LEFT JOIN {civicrm_group_contact} civicrm_contact_civicrm_relationship_civicrm_contact_civicrm_relationship__civicrm_group_contact ON civicrm_contact_civicrm_relationship.id = civicrm_contact_civicrm_relationship_civicrm_contact_civicrm_relationship__civicrm_group_contact.contact_id

So is it me configuring the view wrongly, or is it in the code somewhere, that it's wrong?
And I'm still not sure on how I would go about showing all scholls no mather if there is a related contact person or not.

[andersiversen]

Replying to myself again here

So I did some more testing - it turns out it is as soon as I add the filter for group id, then the duplicate entries shows up; the contact is listed three times pr. group id. It kind of makes sense since the left join making trouble is civicrm_group_contact.  The same thing happens if I add "Group ID" as a field.

Important to note is: This only happens when relationships are used. If I'm just displaying one contact pr row, not using relationships, then the contact is diplayed as it should when using group ID - which is ones pr group the contact is in.

Is "group ID" ment to only be for views displaying groups? I don't think so, because when filtering by group id, it does work in the sense that it only lists contacts there is in the group. Only it lists them three times.

I've tried looking in the code at civicrm/drupal/modules/views, but I'm unable to find the error, if there is one (I think there is).

[petednz]

hi - not sure if this will help - but can you copy the query from the views preview and paste in to your database and see if you get the same outcome - ie is the issue how Views handles the query, or is it deeper in civi?

[andersiversen]

Hi Pete

I have tried what you suggest, and running the query in mysql gives me the triplicates as well. That is when I discovered that removing one of the left joins beginning with "LEFT JOIN {civicrm_group_contact} ..." gave me the right results, with contacts shown just once instead of three times.

This leeds me to believe that the problem is in the way civicrm views integration handles group id together with relationships.

[petednz]

Did you try distinct in the views option - under Advanced - Query Settings - Distinct or Pure Distinct (when you tick Distinct)

[andersiversen]

Awesome Pete ! That did the trick I didn't know about that setting.

Still weird though - I haven't seen this kind of behavior in views before.

Now I just need to figure out how to always display all schools (B contacts) regardless of wether or not there is a student (A Contacts) in group 64 with a relationship to the school.

I can use the drupal module "Views Field View", to embed a view showing the students (if any) as a field. It takes the school's ID as a contextual filter. Then all schools are shown because the relationship settings are moved to the view embeded with Views Field View, and thus all schools are shown regardless of them having a "contact person".
In fact I'm doing this now, but it's not an elegant solution, as views field view builds an entire view for every row in the original view == a lot of database calls.

 

[petednz]

is this a view based on civicontacts or something else?
if you are starting with the school - and then pulling in the students I don't get why you can't have schools where no relationship exists, unless you 'required' the relationship in the Views Relationship

but if that persists - and if it has any benefits to you to have the Schools with no students grouped separately you could use a Views attachment, so you show schools 'with, and schoolls 'without' in separate tables/lists on same page - just a thought

[andersiversen]

Hi Pete

This is a view with civicrm-contacts yes. In one of my posts above I've pasted the full sql-query views generates from my view.
It will only list a school if that school has any related students in group with group_id 64. The relationship is not required.

I don't know how views behaves if it was not contacts, but drupal content instead. Say that the content type "Articles" was related to another content type called "Authors Bio" and I wanted to display all Arthur Bios together with headlines of all the articles they have written, and I still wanted to display the author bio even though the author had not written any articles yet. In our site, we don't have such a relationship in any of our content types, so I haven't tested it, to see what the normal behavior of views relationships is.