Author Topic: How to list a related field (eg employer) in a listing ? (Read 2591 times)

xavier · February 24, 2008, 03:35:51 am

Hello,

My individual contacts are very often related to an organisation (employee of relationship).

I'd like to have a listing of first,last name,email,phone, and Name of the organisation (ie name of the organisation that is employee of the contact)

I'm trying to use the profiles for that, but can't understand how to do it. Could someone put me in the right direction ?

X+

Donald Lobo · February 24, 2008, 11:54:46 am

This is a limitation and you cannot do this with profiles currently. We'll probably fix this in a 2.x release.

lobo

xavier · February 24, 2008, 12:31:42 pm

Ouch,

How do you deal with that ? I suppose there is a workaround ?

X+

Donald Lobo · February 24, 2008, 01:40:51 pm

Unfortunately no easy workaround currently. We hope that someone steps up and figures an elegant solution to the 1..n db issues we've deferred on so we can integrate it in a future 2.x release

lobo

xavier · February 24, 2008, 02:17:26 pm

Well,

That's mostly a 1:1 relation in employee, even so it could be a 1:n (as is household).

I mean, do we really have to create a problem (and solution) that are more complex of what happens where a simple left join could solve the need ?

If one guy works for more than one organisation, then we'll get a special result (not sure what it does, dups ?), and that's not the end of the world, but we cover the 99% easily.

Would it be difficult to add a left join + select criteria in the profiles ?

X+

xavier · February 24, 2008, 02:19:46 pm

Sorry, forgot to paste the "proof of concept" sql:

Code: [Select]

mysql> select employee.first_name,employee.last_name, employer.display_name from civicrm_contact as employee,civicrm_relationship left join civicrm_contact as employer on civicrm_relationship.contact_id_b=employer.id  where civicrm_relationship.contact_id_a=employee.id and civicrm_relationship.relationship_type_id=4;

xavier · February 24, 2008, 02:30:05 pm

And to answer my own question about edge cases (employee of several organisations): you end up with several lines (I believe that's not a big deal).

If you want to avoid it :

Code: [Select]

mysql> select count(employee.id),employee.first_name,employee.last_name, employer.display_name from civicrm_contact as employee,civicrm_relationship left join civicrm_contact as employer on civicrm_relationship.contact_id_b=employer.id  where civicrm_relationship.contact_id_a=employee.id and civicrm_relationship.relationship_type_id=4 group by employee.id;

and if the first column is bigger than 1, you've got a edge case.

X+

Donald Lobo · February 24, 2008, 02:32:59 pm

We will special case employer/employee to solve this issue at some stage as you suggest. All of the profile code actually goes via the general query builder and we've avoided special casing anything.

You should check the queries generated (enable then mysql query log) to see the complexity of our current queries. We need to restructure/simplify the search system a bit to make it more powerful. This is hopefully happening in our 2.1 release

For the profile case, you might want to check the code and contribute a patch. An inefficient patch for this is actually quite trivial. Note that you could also do the below listing relatively easily (and in a non hackish manner) using the custom search feature in 2.. This might be a good test case for you to start coding and contributing back to CiviCRM. A blog entry describing this is coming soon (next week?)

We are on IRC to help out if needed

lobo
lobo

xavier · February 25, 2008, 04:43:40 am

Hi,

Ok, I'll wait until the doc, and try doing the custom search feature on that.

X+

Dave Greenberg · February 27, 2008, 11:39:58 am

I've posted a blog on Custom Search...
http://civicrm.org/node/298

and detailed "step-by-step" documentation...
http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components

I've also added a custom search file which retrieves Employer Name and State for individuals to the beta 3 distribution. This file is used as the reference implementation in the documentation.

It would be very helpful if folks could test-drive the documentation and help improve it. Feedback on the custom search framework for future versions is also most welcome!

xavier · February 27, 2008, 11:17:29 pm

Many thanks Dave,

Beside typing the url for the custom search, is there a standard way of hooking it in the user interface ?

Or even replacing the default format for the search results in some cases (eg if search is filtered on individual, use the custom search that lists the employer ?)

X+

Donald Lobo · February 27, 2008, 11:20:48 pm

X:

there is no standard way to hook it into the user interface as yet. Neither is there a way for automatically triggering it based on various search criteria

lobo

CiviCRM Community Forums (archive)

News:

Author Topic: How to list a related field (eg employer) in a listing ? (Read 2591 times)

xavier

How to list a related field (eg employer) in a listing ?

Donald Lobo

Re: How to list a related field (eg employer) in a listing ?

xavier

Re: How to list a related field (eg employer) in a listing ?

Donald Lobo

Re: How to list a related field (eg employer) in a listing ?

xavier

Re: How to list a related field (eg employer) in a listing ?

xavier

Re: How to list a related field (eg employer) in a listing ?

xavier

Re: How to list a related field (eg employer) in a listing ?

Donald Lobo

Re: How to list a related field (eg employer) in a listing ?

xavier

Re: How to list a related field (eg employer) in a listing ?

Dave Greenberg

Re: How to list a related field (eg employer) in a listing ?

xavier

Re: How to list a related field (eg employer) in a listing ?

Donald Lobo

Re: How to list a related field (eg employer) in a listing ?