Potential bug affecting use of IN operator with APIv3

[EranSch]

Howdy,

I wanted to share something I've observed recently to see if anyone else has encountered this. The steps I'm refering to here are being executed on the wp45.demo.civicrm.org site.

This example uses the Participant entity but I believe I've experienced this issue with Events as well. Using the API explorer, I'm seeing the following unexpected behavior.

Specifying a specific (=) value for a parameter returns the expected result. Using array match (IN => [x, y, z])  returns all records, rather than those matching the supplied values.

Example:

• getcount for event_id=3 role_id=1 returns 8
• getcount for event_id=3 role_id=2 returns 4
• getcount for event_id=3 role_id={"IN":[2,1]} returns 18 (Expecting 12)

I've been dealing with this in various manners however it seems like undesired functionality as I would be expecting a SQL like match of all records with role_id = 2 OR 1.

Anyone else experience this?

[Coleman Watts]

Is this also true of get or does it only affect getcount?

[EranSch]

I used getcount for clarity in the example however get functions just the same.

[Coleman Watts]

Have you done any debugging? It would be great to find out how the api is getting those numbers and correct any faulty logic.

[EranSch]

I've only gone as far as debugging my use of the API in these cases. It seems that the IN operator simply isn't being applied in the query. Rather than returning a subset of the entire matching group, it's as if the parameter simply wasn't included at all.

I'd be up for investigating deeper into the API's logic however I'm not entirely familiar with the inner workings of Civi.

I can probably take some time to look into the source of this but any pointers would also be helpful 

[Eileen]

This is likely related to the specific api. The original api framework (v2) was a hodgepodge of behaviours & formed the basis of api v3. New v3 api were added in a standardised way but many of the api that came from v2 still have 'special' aspects to them. Checking 'new' ones (e.g address, phone, email) should show the underlying getcount with IN functionality. Note that in each version of civi a few more discrepancies have been fixed to make sure you specify the version you are using when referring to them

[EranSch]

Hey Eileen,

That certainly makes sense. In this case, I was able to reproduce the issue on the wp45 demo site which appears to be running 4.5.6

So each entity's API contains its own query logic in terms of how various operators are handled?

[Eileen]

yes & no, the vast majority call the basic_get function ... but there are some exceptions ....

[Coleman Watts]

participant.get api looks like it uses _civicrm_api3_get_query_object() which I think is supposed to handle operators, but only as consistently as the underlying query object does, and some of them are quirky.

[Eileen]

I think Monish did some cleanups in 4.6 on the underlying query object with IN

[EranSch]

Is there a demo server running 4.6?

You guys are awesome, btw 

[xavier]

4.6 isn't stable yet. You can find a dev version of the cutting edge here:
http://dmaster.demo.civicrm.org/
or install it (civicrm-buildkit is probably the easiest)

[EranSch]

I was just hoping to quickly confirm whether or not this functionality is still around in the development branch so the alpha demo server is perfect. Thanks!

EDIT: For the record, it is still present in 4.6alpha1