User Tools


This is an old revision of the document!


Web Service Query Language

Basic Query Language

Since vtiger CRM is a complete business application it needed a more powerful service to retrieve information than the standard REST protocol Retrieve service which works one record at a time. To cover this need vtiger created VQL (Vtigercrm Query Language). This language is a reduced subset of the standard SQL language, with these limitations:

  • sub queries are not supported
  • no mathematical aggregation functions are supported except count(*)
  • we can only execute queries against entities, not direct database tables and once we consult an entity we have access to all it's field no matter how many tables it is separated into in the vtiger CRM database application. There are a few exceptions due to their importance:
    ◦ Currency : we can execute queries against defined currencies in the application
    ◦ Groups : we can get a list of defined groups
    ◦ DocumentFolders : we can get a list of document folders created
    ◦ CompanyDetails: retrieve all the fields values defined in Company Details (Settings).
• the result set is always restricted to 100 records, so the client application will have to use the limit parameter to obtain other subsets.

Even with these limitations it is an incredible and powerful enhancement. The accepted format definition is:

select * | <column_list> | <count(*)>
from <object>
[where <conditionals>] 
[order by <column_list>] [limit [<m>, ]<n>];

The column list in the order by clause can have at most two column names.

* column_list: comma separated list of field names

  • object: module name
  • conditionals: condition operations, in clauses, or like clauses separated by 'and' or 'or' operands these are processed from left to right
  • conditional operators: <, >, ⇐, >=, =, !=
  • in clauses: <column name> in (<value list>)
  • like clauses: <column name> like '<value pattern>'
  • value list: a comma separated list of values
  • m, n: integer values to specify the offset and limit respectively

Try these WSQL commands.

select * from contacts
select firstname,contact_no,phone from contacts
select firstname,contact_no,phone from contacts order by firstname
select count(*) from contacts
/* Only four records starting from the first */
select firstname,contact_no,phone from contacts order by firstname limit 4
/* Only four records starting from the third */
select firstname,contact_no,phone from contacts order by firstname limit 3, 4
select * from groups
select * from companydetails
select * from invoice where hdnGrandTotal > 5000
select * from invoice where hdnGrandTotal > 5000 and invoicestatus = 'Created'
select * from invoice where hdnGrandTotal > 5000 and invoicestatus like '%Created%'

There are four different syntaxes supported.

One is the original query language inherited from vtiger CRM. This is rather limited as it doesn't support related entities nor parenthesis in the conditions.

So we made a first enhancement based on the getRelatedRecords work. This second syntax was created before we started the coreBOS project and was added as a base feature when it was born. You can read about this second syntax below and see it in use in our Customer Portal Extension.

Finally, we enhanced the syntax a third time based on the work done in the QueryGenerator class which gives us a rich and flexible syntax with support for related modules and advanced conditions.

The problem is that in order to maintain backward compatibility we have kept all the syntaxes together and detect dynamically which one to use for each query. This is a bit misleading sometimes.

When a query is sent to the webservice query API it looks for the string “related.”, if this string is found we apply the Related Entity Query Syntax , if the string is not found we look for “not in”, “not null”, “.” or “(”. None of these strings are supported by the query syntax inherited from vtiger CRM so we apply the Extended QueryGenerator syntax. Finally, we apply the original VQL syntax.

So, be careful when sending a query as it can easily be sent to the syntax parser you don't expect.

Let's go over that again.

  • All queries use the list type module name in the FROM and WHERE sections
  • Related Entity Query Syntax is based on the getRelatedRecords web service endpoint, so anything you can do with the query language can be done with this end point. The reverse is not true as the getRelatedRecords method has some functionality that cannot be accessed through the query language. This query language functionality is deprecated and not recommended, it works, and will continue to do so, but the QueryGenerator syntax is more powerful and the recommended way to get information from the query language.
  • Given a web service query, the detection of what parser to use follows these steps:

When a query is sent to the webservice query API it looks for the string “related.”

If this string is found we apply the Related Entity Query Syntax.

SELECT * FROM projecttask WHERE related.project=30x144
SELECT * FROM projecttask WHERE related.project=30x144 AND projecttaskname='dsf'
SELECT * FROM documents WHERE related.accounts=3x12
SELECT * FROM documents WHERE filelocationtype='E' AND related.contacts=4x22
SELECT * FROM Documents WHERE (related.Contacts='4x22') AND (filelocationtype LIKE '%I%') LIMIT 5;
SELECT * FROM modcomments WHERE related.helpdesk=9x114
SELECT * FROM modcomments WHERE related.helpdesk=9x114 AND commentcontent LIKE 'hdcc%'
SELECT * FROM products WHERE related.products=6x58 // ONLY product children are accessible WITH this syntax
SELECT * FROM products WHERE related.contacts=4x22 // ONLY directly related products
SELECT * FROM products WHERE related.contacts=4x22 AND productcategory='Software' // ONLY directly related products
SELECT * FROM Products WHERE related.Contacts='4x22' LIMIT 5;
SELECT * FROM Products WHERE related.Contacts='4x22' ORDER BY productname LIMIT 5;

If the string 'related' is not found we look for “not in”, “not null”, “.” or “(”.

None of these strings are supported by the query syntax inherited from vtiger CRM so we apply the Extended QueryGenerator syntax.

SELECT projectname,modifiedtime
FROM project
WHERE projectname LIKE '%cap%' AND (modifiedtime>'2016-06-30 19:11:59' OR modifiedtime<'2016-07-30 19:11:59')
 
SELECT *
FROM project
WHERE projectname LIKE '%cap%' AND (modifiedtime>'2016-06-30 19:11:59' OR modifiedtime<'2016-07-30 19:11:59')

3.- Query to more than one module(Join module)

There is no “joins” in WebService Query Language,corebos will do them for you.

coreBOS WebService Query Language talks about entities not tables.

We do select…from accounts not select…from vtiger_accounts

Also you have to specify fields, Example Contacts.firstname, Accounts.accountsname

Example

select firstname, Accounts.accountname from Contacts

if you want fields from a related account starting from a contact or an invoice, just ask for them

SELECT * | <column_list> | <COUNT(*)>
FROM 
[WHERE ]
[ORDER BY <column_list>] [LIMIT [, ]];
 
SELECT * FROM Products
WHERE productname='sample' LIMIT 10

Examples

For example, we can launch a query like this:

select projectname,modifiedtime
from project
where projectname like '%o%' and modifiedtime>'2016-06-30 19:11:59'

which will be sent to the original query parser, but we can also send it like this

select projectname,modifiedtime
from project
where (projectname like '%o%' and modifiedtime>'2016-06-30 19:11:59')

which would be parsed by the QueryGenerator class. In this example, both should return the same set of values, but in this example, things are very different:

select projectname,modifiedtime
from project
where projectname like '%cap%' and modifiedtime>'2016-06-30 19:11:59' or modifiedtime<'2016-07-30 19:11:59'
select projectname,modifiedtime
from project
where projectname like '%cap%' and (modifiedtime>'2016-06-30 19:11:59' or modifiedtime<'2016-07-30 19:11:59')

returning a completely different set as the original query parser evaluates the conditions in order of appearance with no parenthesis.

Besides the enhanced syntax, there are two very important things to note about this dynamic switch.

One is that the original query parser and QueryGenerator parser use different ways of identifying a related record. The original parser uses the webservice ID while the QueryGenerator uses the record's entity field name. In other words, when adding conditions on related fields (uitype10), the original parser expects the CRMID of the related record while the QueryGenerator parser expects the value of the entity link field.

For example, this query:

select projecttaskname,projectid,modifiedtime
from ProjectTask
where projectid='33x6772' and  modifiedtime>'2015-08-12 10:10:48' and modifiedtime<'2015-09-12 10:10:48'

looks like this:

but if we add parenthesis and launch the query we get no results returned.

select projecttaskname,projectid,modifiedtime
from ProjectTask
where (projectid='33x6772' and  modifiedtime>'2015-08-12 10:10:48' and modifiedtime<'2015-09-12 10:10:48')

with the parenthesis we are using the QueryGenerator parser and must set the projectid field to the entity link field which is project name. The correct query looks like this:

select projecttaskname,projectid,modifiedtime
from ProjectTask
where (projectid='Owen' and  modifiedtime>'2015-08-12 10:10:48' and modifiedtime<'2015-09-12 10:10:48')

Optionally, we can search directly on the project ID with the extended QueryGenerator syntax:

select projecttaskname,projectid,modifiedtime
from ProjectTask
where (Project.id='33x6772' and  modifiedtime>'2015-08-12 10:10:48' and modifiedtime<'2015-09-12 10:10:48')

The second is that the original query parser will automatically add the “limit 100” to the query while the QueryGenerator will not do this.

You can find a whole set of examples in the coreBOS Web Service Developer Tool and the webservice query unit tests suite.

Query return limit

The select statement only returns 100 records. This is due to timeout and resource restrictions. If you want to obtain more records you must use the limit modifier. Any select statement with a limit modifier will try to return all the records indicated in the limit. So, if we have a contacts table with 150 records, this query:

select * from contacts;

will return 100 records, while this query:

select * from contacts limit 200;

will return the 150 records.

select firstname,contact_no,phone from contacts order by firstname limit 4

Only four records starting from the first

select firstname,contact_no,phone from contacts order by firstname limit 3, 4

Only four records starting from the third

If you are receiving timeouts you can increment the default timeout by modifying the code: PHP
PHP
cURL
cURL

Implemented in ~~issue:127~~


Constructing on top of the getRelatedRecords function we have extended the REST query syntax to benefit from that functionality, making it easy to query related entities and filter them also.

The new syntax enhances the where conditional statement to support module names preceded with the “related” string and followed by the id of the entity:

where related.modulename=id

Examples:

select * from projecttask where related.project=30x144
select * from projecttask where related.project=30x144 and projecttaskname='dsf'
select * from documents where related.accounts=3x12
select * from documents where filelocationtype='E' and related.contacts=4x22
Select * from Documents where (related.Contacts='4x22') AND (filelocationtype LIKE '%I%') LIMIT 5;
select * from modcomments where related.helpdesk=9x114
select * from modcomments where related.helpdesk=9x114 and commentcontent like 'hdcc%'
select * from products where related.products=6x58 // only product children are accessible with this syntax
select * from products where related.contacts=4x22  // only directly related products
select * from products where related.contacts=4x22 and productcategory='Software'  // only directly related products
Select * from Products where related.Contacts='4x22' LIMIT 5;
Select * from Products where related.Contacts='4x22' order by productname LIMIT 5;
  • queryparameters support limit and offset for those sets of related records where the total count is very high or simply high enough to want to be able to page through.
  • queryparameters support column definitions, reducing the size of information being returned
  • multiple entities, IDs or related modules are NOT supported with this API. We have created the extended query language functionality, based on Query Generator for this.

There are a few restrictions we couldn't overcome:

  • only one related entity may be used, as the getRelatedRecords function works with only one entity ID, we inherit this restriction. If more than one is put in the query, only the first is used and the rest are ignored and eliminated.
  • the product relation is limited to directly related records, which means that on a contact we will only have access to the ones on his +info tab, or for a product we can only see it's bundle child products.
  • advanced filtering (limited by current query syntax)

The Webservice extended query language functionality, based on Query GeneratorQuery overcomes some of these limitations.

Searching for empty relations

When you have a webservice query, and you want to select records from a module where a certain UI10 field is empty use:

select * from purchaseorder where po_related_soid = 15x0 

Where 15 is the webservice ID for the module of the UI10 field (in this case salesorders). Then use the 'x', and add a 0.

'po_related_soid' is a placeholder for the fieldname you want to check on, replace that with your own. Of course also replace the module name with your own.

Thanks Luke!

Errors that can be returned

Besides errors that may be returned by the underlying code this function uses, it can return directly these errors:

  • INVALID_MODULE: Given module (module) cannot be found. Either of the two module parameters are incorrect, which one will be specified in the message.
  • ACCESSDENIED: Permission to perform the operation on module (module) is denied. The current user cannot work with one of the two parameter modules
  • INVALIDID: Id specified is incorrect. The given ID does not correspond to an entity in the application.
  • ACCESSDENIED: Permission to read given object is denied. The current user does not have read access to the related records.
  • RECORDNOTFOUND: Record you are trying to access is not found. The given ID is pointing to a deleted record or is incorrect.

coreBOS Documentación