Author Topic: Implementing API search on custom fields for other entities than contacts (Read 948 times)

johanv · March 06, 2015, 01:26:29 am

Hi all,

In our CiviCRM implementation we have a custom field on events, which contains some external ID. Last week I tried to use the api for retrieving events based on this external ID and I found out that custom field searches with the API are only supported for contacts.

Since retrieving an event based on a value in a custom field would be very useful for us, I decided to try implementing this.

It seemed that the search params are processed by _civicrm_api3_dao_set_filter, in api/v3/utils.php. This function calls addSelect() and addWhere() so that the provided DAO (for events CRM_Event_BAO_Event) can return the correct values.

I thought that I could allow searching on custom fields with some extra addJoin() statements, but I couldn't get this working. Maybe it is just impossible, because I think that for using addJoin() with DB_DataObject, you have to know the columns of the tables to join with in advance. This is obviously not the case for the CiviCRM custom field tables. (But I might be wrong about this, because I am kind of clueless about DB_DataObject.)

So then I started looking at how searching on custom fields is implemented for contacts. To retrieve the contacts, instead of using CRM_Contact_BAO_Contact, a custom query is built and executed by _civicrm_api3_get_using_query_object. But although this function takes $entity as a parameter, it only does something useful for contacts.

I created something similar to _civicrm_api3_get_using_query_object, that works on every entity type. It is useful for our use case, but it is probably not ready for merging into CiviCRM-core. I might have re-implemented things that already exist on other places. I might have defined new functions at the wrong place. I didn't check for permissions. I might have broken the CiviCRM architecture. So I post it here, maybe I can improve this with your feedback.

At the moment I only call my new code when the API needs to find an event, and one of the params is referring to a custom field. Becuase that is the use case we need it for, and I want to minimize the impact of the bugs I might have introduced. :-)

But, as said, if you have any feedback about this, it would really be appreciated.

jaapjansma · March 06, 2015, 11:47:33 am

I thought that using the api with a parameter such as custom_xx=value would work on most entities. But if not your solution sounds good and hopefully you could merge it into civicrm one day

johanv · March 06, 2015, 12:41:43 pm

Quote from: jaapjansma on March 06, 2015, 11:47:33 am

I thought that using the api with a parameter such as custom_xx=value would work on most entities.

Nope. Only for contacts

totten · March 06, 2015, 05:42:54 pm

Agree that this is an annoyingly-inconsistent/missing feature. Nice work taking the deep-dive into this.

Understandably, a lot of the code in that branch is string manipulation to build the query. Anytime I see or write code like this, I think, "Dear God Why Do I Have to Write This?" (I'm not saying there's a problem with your code -- it's a general architectural problem.) To answer that question, I've made up a story. The story might not be, eh, "true" or "factual" (I wasn't really around) -- I'm just making it up based on layering/naming/other hints.

1. At first, the idea was to use DB_DataObject to build queries. Who wants to write a new query-builder? Let's use a library! But alas, DB_DataObject sucked at doing JOINs. And the "Advanced Search" system needs a lot of conditional JOINs.

2. So then someone created CRM_*_BAO_Query. This was a new query-builder. It worked fine with lots of conditional JOINs. Hooray! But CRM_*_BAO_Query was tightly tied into the Contact entity. And it's not well-documented or easy to understand.

3. So then someone created "Custom Searches". This was a new query-builder. It's simpler to understand the CRM_*_BAO_Query, but it's still coupled to the Contact entity, and it's not as "mixable". Additionally, it's coupled to a particular UI, so it doesn't work as API.

4. So then someone created "Reports". This was a new query-builder. It works with any entity/table, but it's coupled to a particular UI. It's more "mixable" than "Custom Search" but less mixable than CRM_*_BAO_Query.

5. All the while, lots of people needed to build dynamic queries which didn't fit into any of the buckets. So they did adhoc string-manipulation. And SQL injections were born.

Now, just to keep things interesting, I came into situation #5 several times and eventually contributed a bit to the mess:

6. So then someone (err, me) wrote https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/SQL/Select.php . This query-builder works with JOINs; works with any SQL table; is fairly "mixable"; and isn't coupled to a particular UI. IMHO, if one needs complex query-building in the current version of Civi, this is the least-bad way to do it.

6a. For really dynamic queries (like custom-data), note the example at https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/SQL/Select.php#L14

6b. Examples/tests: https://github.com/civicrm/civicrm-core/blob/master/tests/phpunit/CRM/Utils/SQL/SelectTest.php

6c. There's a little more discussion about it at http://forum.civicrm.org/index.php?topic=31718.0 . Anyway, you might want to look at that and see if it's possible to make the code a little shorter.

totten · March 06, 2015, 06:45:37 pm

Aside: I was a bit lazy in saying CRM_Utils_SQL_Insert the least bad. What I really mean is https://gist.github.com/totten/a9c027c18f1014455a84 .

johanv · March 08, 2015, 02:19:56 pm

Thanks a lot, totten, for the clarification. I will try to rewrite my code with CRM_Utils_SQL_Select somewhere next week, depending on how busy my evenings will be.

johanv · March 09, 2015, 02:26:02 pm

Now I use CRM_Utils_SQL_Select to generate the select query.

I still have to look at the forum thread about the activity API. But it's getting late, that will be for another day.

CiviCRM Community Forums (archive)

News:

Author Topic: Implementing API search on custom fields for other entities than contacts (Read 948 times)

johanv

Implementing API search on custom fields for other entities than contacts

jaapjansma

Re: Implementing API search on custom fields for other entities than contacts

johanv

Re: Implementing API search on custom fields for other entities than contacts

totten

Re: Implementing API search on custom fields for other entities than contacts

totten

Re: Implementing API search on custom fields for other entities than contacts

johanv

Re: Implementing API search on custom fields for other entities than contacts

johanv

Re: Implementing API search on custom fields for other entities than contacts