CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • APIs and Hooks (Moderator: Donald Lobo) »
  • Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
Pages: [1]

Author Topic: Issues when using special operators (LIKE, >=, <=, etc) from the REST interface  (Read 620 times)

capo

  • I post occasionally
  • **
  • Posts: 108
  • Karma: 5
Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
April 23, 2014, 02:22:00 am
I've been taking a look at this forum post about how to use special operators when executing API calls and I've a few questions.

Matches at the beginning of strings
LIKE doesn't works as expected when using wild cards at the beginning of strings. For instance, this REST call:

Code: [Select]
/civicrm/ajax/rest?entity=Address&action=get&debug=1&sequential=1&json=1&street_address[LIKE]=%mb%
is returning all the address that contains "mb" at the middle or at the end of the string, but it's not returning the addresses that starts with "mb". If I want to obtain the addresses that contains "mb" at the beginning, I must remove the initial wild card:

Code: [Select]
/civicrm/ajax/rest?entity=Address&action=get&debug=1&sequential=1&json=1&street_address[LIKE]=mb%
Not sure if it's an issue, a problem on how I am creating the REST call. Or, maybe, it just doesn't work as SQL does?

Examples are inspired from AddressLike.php

Use of <= from the REST interface
I can obtain the contacts who were born the March 22nd, 1980 by using this REST call:

Code: [Select]
/civicrm/ajax/rest?entity=Contact&action=get&debug=1&sequential=1&json=1&birth_date=19800322
But I don't know how can I obtain the contacts who where born since this day. I've tried, for instance:

Code: [Select]
/civicrm/ajax/rest?entity=Contact&action=get&debug=1&sequential=1&json=1&birth_date[>=]=19800322
But the "=" is creating some problems, as it's being translated into:

Code: [Select]
$params = array(
  'version' => 3,
  'sequential' => 1,
  'birth_date[>' => ']',
);
I've tried to solve this by encoding the "=" from the operator:

Code: [Select]
/civicrm/ajax/rest?entity=Contact&action=get&debug=1&sequential=1&json=1&birth_date[>%3D]=19800322
But it doesn't work either, as it's being translated into:

Code: [Select]
$params = array(
  'version' => 3,
  'sequential' => 1,
  'birth_date[>%3D]' => 19800322,
);
Is it a way I can use the <= and >= then using the REST interface?

Use of > for dates from the REST interface

I've had some problems too when trying to obtain contacts who were born after a certain date:

Code: [Select]
/civicrm/ajax/rest?entity=Contact&action=get&debug=1&sequential=1&json=1&birth_date[>]=19800322
But, this time, I'm obtaining the error message:

Quote
birth_date is not a valid date: Array

Can we use this operators from the REST interface?

Obtaining lastest created and modified records

In fact, the example above, wasn't exactly what I wanted to do. My objective was to obtain via REST interface the last modified contacts (so using created_date and modified_date instead of birth_date), but when I try to use them as parameters for filtering, I obtain things like:

Quote
"undefined_fields":["created_date"]

Aren't they allowed fields? (This isn't related to special operators so I probably shouldn't have posted it here, sorry for messing).

xavier

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4453
  • Karma: 161
    • Tech To The People
  • CiviCRM version: yes probably
  • CMS version: drupal
Re: Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
April 23, 2014, 02:39:59 am
Hi,

when it gets tricky on the syntax, I suggest to go the json road: keep the same syntax as you would for a php call (eg params array that might contain arrays that...)

then json_encode and rest.php?json={...}

check out the latest version of the ajax calls, that's how it's done too.
-Hackathon and data journalism about the European parliament 24-26 jan. Watch out the result

capo

  • I post occasionally
  • **
  • Posts: 108
  • Karma: 5
Re: Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
April 23, 2014, 03:08:31 am
Quote
then json_encode and rest.php?json={...}

Is this documented? Can't find references at the REST or AJAX interface wiki pages.

capo

  • I post occasionally
  • **
  • Posts: 108
  • Karma: 5
Re: Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
April 23, 2014, 03:52:33 am
@xavier,

I've just tried that, just to see if I understood what you said:

Code: [Select]
<?php
    $params['api_key'] = 'blablabla';
    $params['key'] = 'blablabla';   
    $params['entity'] = 'Contact';
    $params['action'] = 'get';

    $data = array();
    $data['birth_date'] = '19800322';
    $params['json'] = json_encode($data);
    
    $url = 'https://mysite.com/sites/all/modules/civicrm/extern/rest.php?';
    $url .= http_build_query($params);
    
    $options = array(
        'http' => array(
            'header'  => "Content-type: application/x-www-form-urlencoded\r\n",
            'method'  => 'POST'
        ),
    );
    $context  = stream_context_create($options);
    $result = file_get_contents($url, false, $context);

    var_dump($result);

and it looks like it works! But, if I change the birth_date line into:

Code: [Select]
$data['birth_date'] = array('>=' => '19800322');
It breaks!

Quote
birth_date is not a valid date: Array

Maybe birth_date isn't one of the fields that supports special operators?

xavier

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4453
  • Karma: 161
    • Tech To The People
  • CiviCRM version: yes probably
  • CMS version: drupal
Re: Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
April 23, 2014, 04:17:50 am
Hi,

You can (and should) put the json in the POST, not as a GET.

As for the birth_date, I'm not sure if it's supposed to work directly on needs filter or not covered.

@Eileen, got the definitive answer?

X+
-Hackathon and data journalism about the European parliament 24-26 jan. Watch out the result

Coleman Watts

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 2346
  • Karma: 183
  • CiviCRM version: The Bleeding Edge
  • CMS version: Various
Re: Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
April 23, 2014, 07:07:20 am
I just got done rewriting the api explorer in 4.5 and it uses all the latest-and-greatest syntax. And yes it can generate a json rest request for you using sql operators. Please give it a try and let me know how it works:
http://drupal.sandbox.civicrm.org/civicrm/api/explorer
(another change - it's finally in the menu! under help -> developer)
I would love to hear feedback and bug reports on the api explorer itself - let me know if it generates the correct syntax (whether the individual api in question actually supports that syntax is another question and out-of-scope for my little rewrite).
Thanks.
Try asking your question on the new CiviCRM help site.

capo

  • I post occasionally
  • **
  • Posts: 108
  • Karma: 5
Re: Issues when using special operators (LIKE, >=, <=, etc) from the REST interface
April 24, 2014, 01:54:31 am
Quote from: Coleman Watts on April 23, 2014, 07:07:20 am
Please give it a try and let me know how it works:
http://drupal.sandbox.civicrm.org/civicrm/api/explorer

Works really good and looks much better! Even the "matches at the beginning of the strings" issue that I described in my first post appears to be solved (not sure if it was something related with the API explorer, or with the API itself).

Congrats and thanks!
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion »
  • APIs and Hooks (Moderator: Donald Lobo) »
  • Issues when using special operators (LIKE, >=, <=, etc) from the REST interface

This forum was archived on 2017-11-26.