Author Topic: PHP api issues: sorting, filtering, and more (Read 992 times)

sjthespian · August 30, 2012, 02:29:14 pm

I'm trying to use the v3 API to replace some code I wrote ages ago that runs directly against the database. However, I'm tearing my hair out trying to figure out how to get things working! I've managed to find examples that tell me what I think I should do, but some things just aren't working the way I expect.

I'm still on 4.1.1, it's possible that some of the things below might be fixed in 4.2 (I see that CRM-8514 was just updated a month ago for the options array).

For starters, are all of the columns in the database available for API queries? I can't get is_test or contact_is_deleted to work in queries. The following returns deleted contact records and test memberships for example:

Code: [Select]

$contact = civicrm_api('Contact','Get',array('first_name' => 'Daniel', 'last_name' => 'Rich', 'contact_is_deleted' => 0, 'version' => 3));
$membership = civicrm_api('Membership','Get',array('contact_id' => $id, 'is_test' => 0, 'version' =>3));

I can filter them out of the results, but then I can't use the returned count values for anything.

I can't get sorting or limiting to work on contributions. Take the following:

Code: [Select]

$params = array ('options' => array(
                     'sort' => 'total_amount DESC',
                     'limit' => 5),
                 'sequential' => 1,
                 'version' => 3);
$contr = civicrm_api("Contribution","get", $params);
print_r($contr);

I get 25 results back that I can't figure out how they are being sorted. This also means that I can't get back *more* than 25 results, so I have no way of iterating through contributions.

I found the examples for the LIKE operator, are there more? Let's say I want all contributions before a certain date, is there a way to do something like the following (which doesn't work, btw) or a way to get all contributions in the past year?

Code: [Select]

$params = array ('receive_date' => array('<=' => '2006-08-01'),
                 'sequential' => 1,
                 'version' => 3);

Is there documentation anywhere for the filter values that are implemented and what limitations exist for where they can be used?

The last thing I need I've seen mentioned elsewhere but with no answers. Is there a way to get contribution types? Someone mentioned using OptionType, but the contribution types aren't in there as far as I can tell.

xavier · September 03, 2012, 01:51:22 am

Quote from: sjthespian on August 30, 2012, 02:29:14 pm

I'm trying to use the v3 API to replace some code I wrote ages ago that runs directly against the database. However, I'm tearing my hair out trying to figure out how to get things working! I've managed to find examples that tell me what I think I should do, but some things just aren't working the way I expect.
I'm still on 4.1.1, it's possible that some of the things below might be fixed in 4.2 (I see that CRM-8514 was just updated a month ago for the options array).

If most of the entity/action calls have been implemented in a somewhat generic and easy to understand way, things like filtering and limiting are not (yet). So these area are moving fast and are not well documented/intuitive now.

I would suggest to "play" using the api explorer and simply add and alter params to check easily how it works
( /civicrm/api/explorer on your install on drupal)

Quote

For starters, are all of the columns in the database available for API queries? I can't get is_test or contact_is_deleted to work in queries. The following returns deleted contact records and test memberships for example:
Code: [Select]
$contact = civicrm_api('Contact','Get',array('first_name' => 'Daniel', 'last_name' => 'Rich', 'contact_is_deleted' => 0, 'version' => 3)); $membership = civicrm_api('Membership','Get',array('contact_id' => $id, 'is_test' => 0, 'version' =>3));I can filter them out of the results, but then I can't use the returned count values for anything.

I think it's is_deleted for contact. Use getfields in the api explorer to see what are their expected names

For membership, you have a few magic/undocumented filters (is_current or active_only). Not sure how to document them properly, and they are a bit taken as implemented on the api v2, so we need to think about how we should have them (topic of discussion for our next code sprint end of the month)

Quote

I can't get sorting or limiting to work on contributions. Take the following:
Code: [Select]
$params = array ('options' => array( 'sort' => 'total_amount DESC', 'limit' => 5), 'sequential' => 1, 'version' => 3); $contr = civicrm_api("Contribution","get", $params); print_r($contr);

You might want to migrate first to the latest version, option.limit and option.sort have been changed fairly recently

[/quote]

I found the examples for the LIKE operator, are there more? Let's say I want all contributions before a certain date, is there a way to do something like the following (which doesn't work, btw) or a way to get all contributions in the past year?

Code: [Select]

$params = array ('receive_date' => array('<=' => '2006-08-01'),
                 'sequential' => 1,
                 'version' => 3);

Is there documentation anywhere for the filter values that are implemented and what limitations exist for where they can be used?

[/quote]

Not yet well documented, beside the codes and examples from the unit tests, still brewing.

I think it should be filter ('receive_date'... but not 100% sure

Quote

The last thing I need I've seen mentioned elsewhere but with no answers. Is there a way to get contribution types? Someone mentioned using OptionType, but the contribution types aren't in there as far as I can tell.

I think the code has been coded but not yet contributed/integrated.

(that one should be fairly simple, filtering/sorting not so much)

X+

CiviCRM Community Forums (archive)

News:

Author Topic: PHP api issues: sorting, filtering, and more (Read 992 times)

sjthespian

PHP api issues: sorting, filtering, and more

xavier

Re: PHP api issues: sorting, filtering, and more