Differences

This shows you the differences between two versions of the page.

--- en:devel:corebosws:querylanguage [2020/06/25 16:05]
gmoshi
+++ en:devel:corebosws:querylanguage [2021/10/03 23:39] (current)
joebordes [Query to more than one module (joins)]
@@ Line 1: / Line 1: @@
 ====== Web Service Query Language ======
+===== Query =====
+^Purpose:|Retrieve a set of records with an SQL like language that understands the entities in the system and their relations|
+^Profile:|query(query:String):Object|
+^Send Type:|GET|
+^Parameters:|query: query language command terminated in semicolon|
+^Returns:|array of rows with the results found. each row will contain the same amount of columns corresponding to the query command executed|
+^URL Format:|<code>http://corebos_url/webservice.php?operation=query&query=[query command]</code>|
 ===== 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:
+Since coreBOS is a complete business application it needed a more powerful service to retrieve information than the standard REST protocol could offer, something closer to a GraphQL interface (we didn't have GraphQL back then). To cover this need, a hybrid SQL language was created VQL (Vtigercrm Query Language). This language is a reduced subset of the standard  SQL language with extensions to "understand" the business entities contained in each coreBOS. The limitations are:
-    *there is no "joins" in Web Service Query Language
-    * sub queries are not supported
+  * subqueries are not supported
-    * no mathematical aggregation functions are supported except count(*)
+  * 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.
+  * we can only execute queries against entities, not direct database tables and once we consult an entity we have access to all its fields no matter how many tables it is separated into in the database application. There are some special entities due to their non-standard internal structure:
-    *  There are a few exceptions due to their importance:
+    * **Currency**: we can execute queries against defined currencies in the application
-        * Currency : we can execute queries against defined currencies in the application
+    * **Groups**: we can get a list of defined groups
-        * Groups : we can get a list of defined groups
+    * **DocumentFolders**: we can get a list of document folders created
-        * DocumentFolders : we can get a list of document folders created
+    * **LoginHistory**: historic access data
-        * CompanyDetails: retrieve all the fields values defined in Company Details (Settings).
+    * **AuditTrail**: operational data for all users
-    * the result set is always restricted to 100 records, so the client application will have to use the limit parameter to obtain other subsets.
+    * **Workflow**: list the workflows in the application
-Even with these limitations it is an incredible and powerful enhancement.
+    * **ModTracker**: field-level changes on records
+  * the result set is restricted to 100 records by default. You can overcome this limit by specifically setting a limit clause and by paging the result set (recommended).
+Even with these limitations, it is an incredible and powerful language.
 The accepted format definition is:
-<code>select * | <column_list> | <count(*)>
+<code>select [distinct] * | <column_list> | <count(*)>
 from <object>
 [where <conditionals>]
@@ Line 23: / Line 37: @@
 </code>
-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
-    * column_list: list of fields we want separated by commas
+  * conditionals: condition operations, in clauses, or like clauses separated by 'and' or 'or' operands these are processed from left to right
-    * object: name of the entity to query upon
+    * conditional operators: <, >, <=, >=, =, !=
-    * count(*), returns the total count of records possibly limited by the conditionals
+    * in clauses: <column name> in (<value list>)
-    * conditionals:
+      * value list: a comma-separated list of values
-        * conditional operators:  <, >, <=, >=, =, !=
+    * like clauses: <column name> like '<value pattern>'
-        * in clauses: <column name> in (<value list>)  values separated by commas
+  * the column list in the order by clause can have at most two column names.
-        * like clauses: like 'sqlregex'
+  * m, n: integer values to specify the offset and limit respectively. If only one value is given it is applied as limit
-        * separated by 'and' or 'or' operators
-        * WARNING: all these conditions are executed from left to right, no other precedence rules apply and parenthesis are not accepted.
-    * the order by <column_list> parameter is limited to two columns
-    * offset, limit: are integer values which indicate the offset to start retrieving values and the number of records to return, always limited by the 100 maximum restriction. NOTE, if only one value is given it is applied as <limit>
-Open the webservice developer tool application and lets try some VQL.
-Once validated against our coreBos the program will permit us to introduce VQL directly and see the result.
 {{:en:devel:corebosws:coreboswebservicequery.png|}}
@@ Line 52: / Line 60: @@
 select firstname,contact_no,phone from contacts order by firstname limit 3, 4
 select * from groups
-select * from companydetails
+select * from workflow
 select * from invoice where hdnGrandTotal > 5000
 select * from invoice where hdnGrandTotal > 5000 and invoicestatus = 'Created'
@@ Line 58: / Line 66: @@
 </code>
-There are **three different syntaxes** supported.
+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.
@@ Line 77: / Line 85: @@
   * All queries use the list type module name in the FROM and WHERE sections
-  * [[en:devel:corebosws:querylanguage#related_entity_query_syntax|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.
+  * [[en:devel:corebosws:querylanguage#related_entity_query_syntax|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 endpoint. 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:
 === 1.- Related modules ===
-When a query is sent to the webservice query API it looks for the string "related."
+When a query is sent to the web service query API it looks for the string "related."
 If this string is found we apply the Related Entity Query Syntax.
@@ Line 117: / Line 125: @@
 </code>
-=== 3.- Original VQL syntax (no 'related') ===
+=== 3.- Use legacy VQL ===
-<code SQL>
+===== Query to more than one module (joins) =====
-select * | <column_list> | <count(*)>
-from
-[where ]
-[order by <column_list>] [limit [, ]];
-SELECT * FROM Products
+There are no "joins" in WebService Query Language, but coreBOS will do them for you. Remember that coreBOS WebService Query Language talks about entities, not tables.
-where productname='sample' LIMIT 10
-</code>
+We do <wrap lo>select...from accounts</wrap>  not  <wrap lo>select...from vtiger_accounts</wrap>
+In this same line, you have to specify fields prefixed by their module name and WSQL will do whatever it has to do to get the field from the directly related module. For example
+<code>select firstname, Accounts.accountname from Contacts;</code>
+Note, that fields on the module **DO NOT** have a prefix. The result of this query is VERY different:
+<code>select Contacts.firstname, Accounts.accountname from Contacts;</code>
+In this second query, we are accessing the first name of the Contact RELATED to the contacts. The Contact module has a related field to itself (Reports To), the column in the query above is retrieving the name from the RELATED contact through the Reports To field, not the name on the contact record itself.
+So, if you want fields from a related module, just ask for them
 === Examples ===
-For example, we can launch a query like this:
+<code SQL>
+SELECT * FROM Products where productname='sample' LIMIT 10;
+</code>
 <code>
@@ Line 165: / Line 183: @@
 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.
+**One** is that the original query parser and QueryGenerator parser use different ways of identifying a related record. The original parser uses the web service 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:
@@ Line 187: / Line 205: @@
 </code>
-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:
+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:
 <code>
@@ Line 208: / Line 226: @@
 You can find a whole set of examples in the [[en:devel:corebosws:coreboswsbrowser|coreBOS Web Service Developer Tool]] and the [[https://github.com/tsolucio/coreBOSTests/blob/master/include/Webservices/VtigerModuleOperation_QueryTest.php|webservice query unit tests suite]].
-==== Query return limit ====
+===== 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:
@@ Line 219: / Line 239: @@
 will return the 150 records.
-<code>
+<code>select firstname,contact_no,phone from contacts order by firstname limit 4;</code>
-select firstname,contact_no,phone from contacts order by firstname limit 4</code>
 Only four records starting from the first
-<code>
-select firstname,contact_no,phone from contacts order by firstname limit 3, 4
+<code>select firstname,contact_no,phone from contacts order by firstname limit 3, 4</code>
-</code>
- Only four records starting from the third
+Only four records starting from the third
 <WRAP center round info 75%>If you are receiving timeouts you can increment the default timeout by modifying the code:
 [[https://github.com/tsolucio/coreBOSwsLibrary/blob/master/php/Net/HTTP_Client.php#L22|PHP]]\\
@@ Line 234: / Line 255: @@
 ===== Related Entity Query Syntax =====
-Implemented in ~~issue:127~~
 {{youtube>5B0A6IPMnJM}}
@@ Line 265: / Line 284: @@
   * 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.
@@ Line 279: / Line 298: @@
 ===== 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:
+When you have a web service query, and you want to select records from a module where a certain related (UI10) field is empty use:
 <code>
-select * from purchaseorder where po_related_soid = 15x0
+select * from PurchaseOrder where po_related_soid = 15x0
 </code>
-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.
+Where 15 is the web service ID for the module of the UI10 field (in this case SalesOrder). 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.
+'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.
 [[https://discussions.corebos.org/showthread.php?tid=912|Thanks Luke!]]
@@ Line 295: / Line 314: @@
 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.
+  * **INVALID_MODULE**: Given module (module) cannot be found, 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
+  * **ACCESSDENIED**: Permission to perform the operation on module (module) is denied. The current user cannot work with one of the 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.
+  * **ACCESSDENIED**: Permission to read given object is denied.  The current user does not have read access to the records.
-  * **RECORDNOTFOUND**: Record you are trying to access is not found. The given ID is pointing to a deleted record or is incorrect.
+  * **RECORDNOTFOUND**: The record you are trying to access is not found. The given ID is pointing to a deleted record or is incorrect.
+===== Connecting to the web service API with Postman =====
+Using [[https://www.postman.com/|Postman]] to access the coreBOS Web service API is rather easy. The main trick to keep in mind is that GET requests parameters go in the PARAMS tab and POST requests parameters go in the BODY tab.
+The [[en:devel:corebosws:login|Login is a two-step process]] so we have to create two requests and manually calculate the access token MD5.
+Here you can see the Challenge setup:
+{{ :en:devel:corebosws:postmanchallenge.png |GetChallenge}}
+Here you can see the Challenge setup:
+{{ :en:devel:corebosws:postmanlogin.png |Login}}
+Here you can see the Challenge setup:
+{{ :en:devel:corebosws:postmanquery.png |Query}}
+And here you can see a quick demo of how to do it:
+{{youtube>z4UZw0eQ7Kw}}
+----
+<WRAP right>
+[[:en:devel:corebosws:methodreference|Next: Method Reference]] | [[en:devel:corebosws:tableofcontents|Table of Contents]]
+</WRAP>
+----

User Tools

Differences

Page Tools

Site Tools