Author Topic: Avoid Query in Rest API (Read 2781 times)

Mohit Thakral · February 15, 2012, 06:35:16 pm

I was trying to use rest api v3 to get all the groups using following url (can't post links, so removed the initial part)

/drupal624/sites/all/modules/civicrm/extern/rest.php?q=civicrm/group/get&key=9e2a19a64b1c8f18847343432af9f704&api_key=6d5d08c5505fd35452aff00bfab13d90&version=3&json=1

the output seems to have lot of data, like queries as well. Not even that If i remove json=1, causing API to return XML data. Seems like XML is not valid XML. Since, chrome was not able to parse it.

I have 2 questions.

How Can I remove the queries being spit out.
How can i request, to return only one or 2 columns lets say only group name.

Thanks a lot for reading this, and it will be great if you some one can provide me details.

Regards
Mohit Thakral

Erik Hommel · February 15, 2012, 11:20:23 pm

The XML return of the API needs some work, there are some bugs outstanding. I would have a test with Firefox rather than Chrome, it tends to give you a little more feedback in these cases. I am not sure what you mean by having queries in your output, can you include the output you get in your post?

You can find a description of how to get only a couple of fields in this blog: http://civicrm.org/blogs/eileen/api-documentation-state-api

xavier · February 16, 2012, 01:19:37 am

Hi,

We are moving to the syntax

/drupal624/sites/all/modules/civicrm/extern/rest.php?entity=group&action=get&key=9e2a19a64b1c8f18847343432af9f704&api_key=6d5d08c5505fd35452aff00bfab13d90&version=3&json=1

Could you tell me where you found the link so we can update the documentation/tool?

you can (in most) add a &return=field1,field2 in the url Some older methods haven't been updated to handle this yet.

X+

Mohit Thakral · February 16, 2012, 06:26:35 am

Thanks Xavier, for the response.

This project I have been working on was initially developed by someone else. So, I am not very sure of the documentation source. But it was using the version 2 api .

But, I will fix my urls.

One more thing, I observed was that if I just pass the api_key and site key(or key) along with the query it works. Even if the user never logged, is that the expected behavior.

Thanks for pointing to the documentation. I had a look at it in past. But could not figure out api explorer url. Since, on my devbox clean urls were not enabled. If in case clean urls are not enabled then one can access it using ://<Drupal-Install-Root>/?q=civicrm/api/doc#explorer

I tried "://ubuntu-mt/drupal624/sites/all/modules/civicrm/extern/rest.php?json=1&debug=1&version=3&entity=Group&action=get&user=admin&pwd=mohit123&key=9e2a19a64b1c8f18847343432af9f704". This url was generated by the API explorer

It gave me following error.

{
"error_message":"Unknown function invocation.",
"is_error":1
}

But If I use.. "://ubuntu-mt/drupal624/sites/all/modules/civicrm/extern/rest.php?q=civicrm/group/get&key=9e2a19a64b1c8f18847343432af9f704&api_key=6d5d08c5505fd35452aff00bfab13d90&version=3&json=1&debug=1&return=name". Then, first thing "return" param has no effect it gives me following pretty print JSON (formatted JSON ). With so much of data and by queries I meant the "where_clause" and "select_tables" in JSON output . I just want the names of the groups.

Just to let you know. I am working on CiviSync from targetintegration.com, trying to complete it asap. On my devbox I am using drupal 6.2.4 CiviCrm 3.4.5.

It will be great if you can allow me to post links

. For the time being in the urls think as :// => http://

{
   "is_error":0,
   "version":3,
   "count":5,
   "values":{
      "1":{
         "id":"1",
         "name":"Administrators",
         "title":"Administrators",
         "description":"Contacts in this group are assigned Administrator role permissions.",
         "source":null,
         "saved_search_id":null,
         "is_active":"1",
         "visibility":"User and User Admin Only",
         "where_clause":null,
         "select_tables":null,
         "where_tables":null,
         "group_type":["1"],
         "cache_date":null,
         "parents":null,
         "children":null,
         "is_hidden":"0"
      },
      "2":{
         "id":"2",
         "name":"Newsletter Subscribers",
         "title":"Newsletter Subscribers",
         "description":null,
         "source":null,
         "saved_search_id":null,
         "is_active":"1",
         "visibility":"Public Pages",
         "where_clause":" ( `civicrm_group_contact-2`.group_id IN ( 2 ) AND `civicrm_group_contact-2`.status IN (\"Added\") ) ",
         "select_tables":"a:12:{s:15:\"civicrm_contact\";i:1;s:15:\"civicrm_address\";i:1;s:22:\"civicrm_state_province\";i:1;s:15:\"civicrm_country\";i:1;s:13:\"civicrm_email\";i:1;s:13:\"civicrm_phone\";i:1;s:10:\"civicrm_im\";i:1;s:19:\"civicrm_worldregion\";i:1;s:25:\"`civicrm_group_contact-2`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-2` ON contact_a.id = `civicrm_group_contact-2`.contact_id \";s:6:\"gender\";i:1;s:17:\"individual_prefix\";i:1;s:17:\"individual_suffix\";i:1;}",
         "where_tables":"a:2:{s:15:\"civicrm_contact\";i:1;s:25:\"`civicrm_group_contact-2`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-2` ON contact_a.id = `civicrm_group_contact-2`.contact_id \";}",
         "group_type":["1","2"],
         "cache_date":null,
         "parents":null,
         "children":null,
         "is_hidden":"0"
      },
      "3":{
         "id":"3",
         "name":"Summer Program Volunteers",
         "title":"Summer Program Volunteers",
         "description":null,
         "source":null,
         "saved_search_id":null,
         "is_active":"1",
         "visibility":"Public Pages",
         "where_clause":" ( `civicrm_group_contact-3`.group_id IN ( 3 ) AND `civicrm_group_contact-3`.status IN (\"Added\") ) ",
         "select_tables":"a:12:{s:15:\"civicrm_contact\";i:1;s:15:\"civicrm_address\";i:1;s:22:\"civicrm_state_province\";i:1;s:15:\"civicrm_country\";i:1;s:13:\"civicrm_email\";i:1;s:13:\"civicrm_phone\";i:1;s:10:\"civicrm_im\";i:1;s:19:\"civicrm_worldregion\";i:1;s:25:\"`civicrm_group_contact-3`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-3` ON contact_a.id = `civicrm_group_contact-3`.contact_id \";s:6:\"gender\";i:1;s:17:\"individual_prefix\";i:1;s:17:\"individual_suffix\";i:1;}",
         "where_tables":"a:2:{s:15:\"civicrm_contact\";i:1;s:25:\"`civicrm_group_contact-3`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-3` ON contact_a.id = `civicrm_group_contact-3`.contact_id \";}",
         "group_type":["1","2"],
         "cache_date":null,
         "parents":null,
         "children":null,
         "is_hidden":"0"
      },
      "4":{
         "id":"4",
         "name":"Advisory Board",
         "title":"Advisory Board",
         "description":null,
         "source":null,
         "saved_search_id":null,
         "is_active":"1",
         "visibility":"Public Pages",
         "where_clause":" ( `civicrm_group_contact-4`.group_id IN ( 4 ) AND `civicrm_group_contact-4`.status IN (\"Added\") ) ",
         "select_tables":"a:12:{s:15:\"civicrm_contact\";i:1;s:15:\"civicrm_address\";i:1;s:22:\"civicrm_state_province\";i:1;s:15:\"civicrm_country\";i:1;s:13:\"civicrm_email\";i:1;s:13:\"civicrm_phone\";i:1;s:10:\"civicrm_im\";i:1;s:19:\"civicrm_worldregion\";i:1;s:25:\"`civicrm_group_contact-4`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-4` ON contact_a.id = `civicrm_group_contact-4`.contact_id \";s:6:\"gender\";i:1;s:17:\"individual_prefix\";i:1;s:17:\"individual_suffix\";i:1;}",
         "where_tables":"a:2:{s:15:\"civicrm_contact\";i:1;s:25:\"`civicrm_group_contact-4`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-4` ON contact_a.id = `civicrm_group_contact-4`.contact_id \";}",
         "group_type":["1","2"],
         "cache_date":null,
         "parents":null,
         "children":null,
         "is_hidden":"0"
      },
      "5":{
         "id":"5",
         "name":"Case_Resources",
         "title":"Case Resources",
         "description":"Contacts in this group are listed with their phone number and email when viewing case. You also can send copies of case activities to these contacts.",
         "source":null,
         "saved_search_id":null,
         "is_active":"1",
         "visibility":"User and User Admin Only",
         "where_clause":" ( `civicrm_group_contact-5`.group_id IN ( 5 ) AND `civicrm_group_contact-5`.status IN (\"Added\") ) ",
         "select_tables":"a:10:{s:15:\"civicrm_contact\";i:1;s:15:\"civicrm_address\";i:1;s:22:\"civicrm_state_province\";i:1;s:15:\"civicrm_country\";i:1;s:13:\"civicrm_email\";i:1;s:13:\"civicrm_phone\";i:1;s:10:\"civicrm_im\";i:1;s:19:\"civicrm_worldregion\";i:1;s:25:\"`civicrm_group_contact-5`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-5` ON contact_a.id = `civicrm_group_contact-5`.contact_id \";s:6:\"gender\";i:1;}",
         "where_tables":"a:2:{s:15:\"civicrm_contact\";i:1;s:25:\"`civicrm_group_contact-5`\";s:114:\" LEFT JOIN civicrm_group_contact `civicrm_group_contact-5` ON contact_a.id = `civicrm_group_contact-5`.contact_id \";}",
         "group_type":["2"],
         "cache_date":null,
         "parents":null,
         "children":null,
         "is_hidden":"0"
      }
   }
}

Erik Hommel · February 16, 2012, 07:02:07 am

Quote

just pass the api_key and site key(or key) along with the query it works. Even if the user never logged,

At the moment it is, you normally get the api_key when you log in but if you know it anyway you get in. We would like to replace it with a little more robust authentication (like OAuth?), any ideas on the subject?

As far as the names go, try this?

Code: [Select]

://ubuntu-mt/drupal624/sites/all/modules/civicrm/extern/rest.php?q=civicrm/group/get&key=9e2a19a64b1c8f18847343432af9f704&api_key=6d5d08c5505fd35452aff00bfab13d90&version=3&json=1&debug=1&return.name

Mohit Thakral · February 16, 2012, 09:10:42 am

Code: [Select]

://ubuntu-mt/drupal624/sites/all/modules/civicrm/extern/rest.php?q=civicrm/group/get&key=9e2a19a64b1c8f18847343432af9f704&api_key=6d5d08c5505fd35452aff00bfab13d90&version=3&json=1&debug=1&return.name

Didn't work as well. Do you think I should debug and verify.

As far as OAuth I concerened I have implemented OAuth 2.0 using Asp.Net MVC in .Net, I might be able to once i m done with CiviSync

.

Regards
Mohit

xavier · February 16, 2012, 11:19:12 pm

Ok, as you've seen, the rest interface needs some love. Part of the issue is that no one knew how it was supposed to work (several half working solutions) and took us a while to realise. Anyway my understanding of the consensus (and I might be stretching the meaning of it to cover "what makes sense for me given the situation") is

1) having the user & site key is enough to login on any request
2) the rest default to api v3
3) it accepts both GET & POST
4) the params are entity & action... and pretty much exactly the ones you have for the ajax interface and the api explorer

Could you try copying the rest.php file for the 4.1 and see if the syntax work?
/drupal624/sites/all/modules/civicrm/extern/rest.php?entity=group&action=get&key=9e2a19a64b1c8f18847343432af9f704&api_key=6d5d08c5505fd35452aff00bfab13d90&version=3&json=1

Erik Hommel · February 17, 2012, 01:23:56 am

I agree with Xavier, and it would be great it you could contribute the OAuth stuff

Mohit Thakral · February 17, 2012, 05:55:57 am

Sure, would love to do that but for now CiviSync..

Following are my findings for version 3.4.5

When I send

http://ubuntu-mt/drupal624/sites/all/modules/civicrm/extern/rest.php?q=civicrm/group/get&title=%&json=1&key=9e2a19a64b1c8f18847343432af9f704&api_key=6d5d08c5505fd35452aff00bfab13d90&version=3&return[name]

At 391 in /var/www/drupal624/sites/all/modules/civicrm/CRM/Utils/REST.php inside methogs "&buildParamList"

return[name] of querystring is added to $params as "return.name"=1

After that a call to method "civicrm_api3_group_get" is made, which is inside /var/www/drupal624/sites/all/modules/civicrm/api/v3/Group.php at line 85

/var/www/drupal624/sites/all/modules/civicrm/extern/rest.php line 47 $rest->run();
goes to
/var/www/drupal624/sites/all/modules/civicrm/CRM/Utils/REST.php line 136 self::handle();

which calls self::process($args) in the same file at line 322 at this point $args has 3 values civicrm,group,get

now "self::process($args)" calls "& self::buildParamList( )" at line 326 same file

"self::buildParamList()" is also in the same file at line 379 ,

at line 391 in the "self::buildParamList()" it checks for querystring param "return" and makes sure that it has to be array and then sets it back to $params as "return.key" =1

once the control is back in "self::process($args)" it calls $result = civicrm_api ($args[1], $args[2],$params); at line 367 in /var/www/drupal624/sites/all/modules/civicrm/CRM/Utils/REST.php, at this time $param has one return as array and one return.name set to 1. [This is what causes issue, ]

then at line 133 in /var/www/drupal624/sites/all/modules/civicrm/api/api.php function "civicrm_api3_group_get" is invoked , using $function variable,

Now "civicrm_api3_group_get" inside /var/www/drupal624/sites/all/modules/civicrm/api/v3/Group.php at line 89-94 converts the "return.name" set in step x to $returnProperties, but $params still has return array with name set to empty string as passed in the query

now get groups is called at /var/www/drupal624/sites/all/modules/civicrm/CRM/Contact/BAO/Group.php.CRM_Contact_BAO_Group::getGroups:254    with checks the params says it has an array "return" and converts that to an "in" whereAdd at line 261

then at line 272 in /var/www/drupal624/sites/all/modules/civicrm/CRM/Contact/BAO/Group.php find is called which calls the dbquery

finally the query generated is "SELECT name,id FROM civicrm_group WHERE ( title LIKE "%" ) AND ( return IN () ) AND ( civicrm_group.is_active = 1 ) "

Following is the call stack

/var/www/drupal624/sites/all/modules/civicrm/packages/DB/DataObject.php.DB_DataObject->_query:2424
/var/www/drupal624/sites/all/modules/civicrm/packages/DB/DataObject.php.DB_DataObject->find:442
/var/www/drupal624/sites/all/modules/civicrm/CRM/Contact/BAO/Group.php.CRM_Contact_BAO_Group::getGroups:272
/var/www/drupal624/sites/all/modules/civicrm/api/v3/Group.php.civicrm_api3_group_get:100
/var/www/drupal624/sites/all/modules/civicrm/api/api.php.civicrm_api:134
/var/www/drupal624/sites/all/modules/civicrm/CRM/Utils/REST.php.CRM_Utils_REST->process:367
/var/www/drupal624/sites/all/modules/civicrm/CRM/Utils/REST.php.CRM_Utils_REST->handle:322
/var/www/drupal624/sites/all/modules/civicrm/CRM/Utils/REST.php.CRM_Utils_REST->run:136
/var/www/drupal624/sites/all/modules/civicrm/extern/rest.php.{main}:47

Can we do something about this ?, Or there is not way i can get individual columns using rest

CiviCRM Community Forums (archive)

News:

Author Topic: Avoid Query in Rest API (Read 2781 times)

Mohit Thakral

Avoid Query in Rest API

Erik Hommel

Re: Avoid Query in Rest API

xavier

Re: Avoid Query in Rest API

Mohit Thakral

Re: Avoid Query in Rest API

Erik Hommel

Re: Avoid Query in Rest API

Mohit Thakral

Re: Avoid Query in Rest API

xavier

Re: Avoid Query in Rest API

Erik Hommel

Re: Avoid Query in Rest API

Mohit Thakral

Re: Avoid Query in Rest API