This is an old revision of the document!
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
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.
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')
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
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
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.
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
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;
There are a few restrictions we couldn't overcome:
The Webservice extended query language functionality, based on Query GeneratorQuery overcomes some of these limitations.
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.
Besides errors that may be returned by the underlying code this function uses, it can return directly these errors: