Searching for related contacts: Employee of does not return results when extra criteria is provided

The sample data shows Shauna Barkley as an employee of Hubbard Education Center.

If I perform an advanced search, choose "Display contacts as": "related contacts" and choose "Employee of" as the relationship and then hit search. I get a lot of results, including Shauna (as expected).

However, if I add to the criteria "Complete or Partial Name": "Hubbard" or even "Organization type": "Organization" I get zero results.

I noticed this isssue when upgrading from 5.33 to 5.39, so I can't be sure it's a regression with 5.39 but possibly?

@jamie thanks for reporting the issue are you able to provide a fix for this?

added comp:Search regression sig:bug triaged labels

I have given this the regression label as this might be a regression. I am not completely sure if it is one. So feel free to remove the regression label if this is not a regression.

Unfortunately, I'm leaving on a family vacation tomorrow so I won't have a chance to give this any attention until the week of Aug. 2 :(.

No problem. Enjoy your holiday!

I just tested and this isn't a regression (or not recent if it is) - I tried on demo sites for 5.33 & master side by side

In both cases I got zero results for this query

And if I changed to 'Individual' I got the same results as if I didn't filter by contact type.

If I switch to 'employer of' the organization filter works

ie. it seems to be applying the criteria against the contact to be displayed. I have no idea what it should do - I find it very confusing - but it hasn't changed

removed regression label

Thanks for looking into this @eileen.

I think there is a very subtle difference in your search. I think you are taking these steps:

1. Click Search -> Advanced Search
2. Entering "Organization" in Contact Type Field
3. Expanding the "Relationships" accordian and selecting "Employee of"

I can't get that to work on 5.33 either.

But, this does seem to work on 5.33:

1. Click Search -> Advanced Search
2. Under "Display results as" choose "Related Contacts" and choose "Employee of" as the relationship type.
3. Then select "Organization" as the contact type.

My results page describes the query this way:

(This screen grab is from a random site running 5.33, not the demo data.)

I think this is the line and commit causing the problem. If we append $qcache['where'] to the passed in $where variable, we get a where statement that overly restricts the results. @jaapjansma - was that something done on purporse or is it possibly a mistake?

Hopefully this PR solves the problem without stepping on the original commit:

https://github.com/civicrm/civicrm-core/pull/20997

Thanks @jamie for finding this out. I can test your PR after the holiday.

Adding some notes:

The problem seems to come up for additional criteria in certain tables, but there doesn't seem to be a pattern as to which tables.

For example if the additional criteria is address.country = something, then it DOES return employees of orgs that have an address in that country. Or if you restrict the relationship start date then it works. But as above if you do partial name or contact type, or email, it doesn't work.

added regression label

I don't know if this helps or makes it more confusing but you can think about what PR 20002 was trying to achieve without getting into cases and relationship head-scratching if it's rephrased like this:

1. Alice has red card and a blue card.
2. Bob's name is written on the blue card but not the red one.
3. Now do a search for red cards and display the names written on them.
4. You wouldn't expect Bob to be in the results.

And this seems to match @jamie what you were saying on the PR: "first, find everyone who matches the criteria (red cards). Second, find everyone with the given relationship to them (is listed on the card)".

Now do a straight replace of the above with cases and what was happening was Bob was showing up:

1. Alice has a case of type housing and another of type daycare.
2. Bob is a benefit specialist on the daycare but not the housing.
3. Now do a search for housing and display the benefit specialists.
4. You wouldn't expect Bob to be in the results.

So yes maybe PR 20002 isn't the right solution, but the above makes it clearer to me what it was trying to do.

changed milestone to %5.40.0

closed

@DaveD I think it's all helpful :).

The part of your analogy that I'm struggling with is this line:

3. Now do a search for housing and display the benefits specialists.

I don't think you are searching for "housing" or even "housing cases". You are searching for contacts with a housing case. Once you have your list of contacts, I think of them as being context free. So, when you then find the benefits specialist for these contacts, you find all the related benefits specialists.

Ideally, I think the "Display related contacts" should work this way.

What's different about the case search compared to all other related contact searches is that there is additional context to the relationship - specifically, the case id.

I wonder if we should expose both case id and case type in the relationships accordian on the advanced search? Then you could achive this search by expanding the relationships accordian, choosing "Benefits Specialist" as the relationship type and "Housing" as the case type (or you could use auto complete to pick one or more specific cases to get all contacts related to the case).

This is a very good point.

I think https://github.com/civicrm/civicrm-core/pull/18779 then falls into the same boat.

marked this issue as related to report#72 (moved)

mentioned in issue report#72 (moved)

mentioned in issue report#77 (moved)

mentioned in issue #3228 (closed)