Query

This function is used to query the database. Celoxis API has two ways to query the database.

One is a simple version where you specify the table and the filter conditions .
In the other way, you write the SQL yourselves. This is available only for install-able/On-premise version customers.

Function name

query

Parameters

table (Required)

One of the valid table names as mentioned in the table reference or generic for running any SQL query (only for On-Premise customers).

Filtering conditions (Optional)

You can specify filter conditions for rows in one of the following formats:

1. <column-name>=<value>
2. <table-name>.<column-name>=<value>
3. <table-name>.<column-name>.<operator>=<value>

In case of 1, table name is assumed to be the table parameter. In case of 1 and 2 the operator is assumed to be '='. You can have operators like '=', '>', '<', '>=', '<=' and '!='. For example, db_task.plan_start.>=2008-10-1 would mean tasks that start after 1st of October 2008.

You can specify multiple such conditions, separated by the '&' sign. If you specify the column name more than once, it is assumed that you want an 'OR' condition between its multiple values. For example to fetch a list of incomplete tasks in project whose id is 200 you should specify:

api.do?function=query&table=db_task&project_id=200&db_task.percent_complete.<=100

Below query will return names of users who have a login in the application.

api.do?function=query&table=generic&sql=select name from db_person where login_name IS NOT NULL

This query will show tasks which do not have 'Details' entered i.e details field is blank.

api.do?function=query&table=generic&sql=select summary from db_task where detail like ''

join-with (Optional)

If you want the table to be joined with another table, you specify the 'join-with' parameter. This will be of the form <table-name>.<column-name>. The main table (specified by the table parameter) will be joined on its primary key with the table and column specified with the 'join-with' parameter. For example to fetch a list of tasks assigned to user with id 7 with project phase 1, you should specify :

api.do?function=query&table=db_task&join-with=db_assignment.task_id&db_assignment.user_id=7&db_task.project_phase_id=1

order-by (Optional)

The column on which to order the results by. You can even append asc or desc to the column to sort that column in ascending or descending order. Separate multiple columns by comma. For example, in case of the db_task table, to order the tasks by plan_start ascending and plan_finish descending, specifying the order-by parameter as plan_start asc, plan_finish desc. This is not applicable when sql is specified.

where (Optional)

The SQL where clause. This provides more flexibility than filter parameters.

sql (Optional)

The entire SQL query. This is only available for On-Premise customers.

skip (Optional)

The number of rows to skip before returning the remaining rows. This is not applicable when sql is specified.

take (Optional)

The number of rows to return. This is not applicable when sql is specified.

Return values:

In case of XML data format, you will be returned an XML string with two elements: count (the number of rows) and rows (this will contain multiple row elements). In addition to the requested columns, computed values (see table reference for more information) and custom field values are also returned. For example

<result>
  <count>8</count>
   <rows>
      <row>
        <f><n>id</n><v>3246247</v></f>
        <f><n>guid</n><v>156E02DD-66B8-476E-8083-B86120BD99</v></f>
        <f><n>vdb_id</n><v>10961</v></f>
        <f><n>created</n><v>2012-10-09T06:03:37</v></f>
        <f><n>ts</n><v>2013-10-17T01:55:36</v></f>
        <f><n>creator_id</n><v/></f>
        <f><n>project_id</n><v>111829</v></f>
        <f><n>src</n><v/></f>
        <f><n>parent_id</n><v/></f>
        <f><n>folder_id</n><v>1199815</v></f>
        <f><n>priority</n><v>3</v></f>
        <f><n>summary</n><v>T</v></f>
        <f><n>detail</n><v/></f><f></row>
       ...
       ... 
      </row>
   </rows>
</result>

In case of JSON, it will return an object with two properties: count (the number of rows) and rows (an array of objects).

{"count":8,
   "rows": 
     [
       [
         {"v":"3246247","n":"id"}, 
         {"v":"156E02DD-66B8-476E-8083-F2B86120BD99","n":"guid"},
         {"v":"10961","n":"vdb_id"},
         {"v":"2012-10-09T06:03:37","n":"created"},
         {"v":"2013-10-17T01:55:36","n":"ts"},
         {"v":"","n":"creator_id"},
         {"v":"111829","n":"project_id"},
         {"v":"","n":"src"},
         {"v":"","n":"parent_id"},
         {"v":"1199815","n":"folder_id"},
         {"v":"3","n":"priority"},
         {"v":"T","n":"summary"},
         {"v":"","n":"detail"},
         ...
         ...
       ]
     ]
}