FQL

1. What is FQL

• FQL is a SQL-like query statement that can be used in APL. Developers can use FQL in APL to query business data in enterprises

Two, FQL syntax

• Currently supported syntax:

  SELECT
      fieldname1 [, fieldname2 ...]
      [FROM object apiName
      [WHERE where_condition]
      [ORDER BY {col_name | expr | position}
        [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  

  Notes

  • does not support select * to query all fields, only select field1, field2... to find relevant fields on demand
  • limit defaults to 10, the maximum value is 100, offset defaults to 0
  • The use of the OR condition needs to be activated separately, please contact the sales staff to place an order to activate the product: [object list filtering support or]

3. Operators supported by Where conditions

• AND query: AND

select name from object_227xW__c where (field_rzv5M__c is null and field_rzv5M__c <= 100) order by last_modified_time desc limit 10 offset 0;

• Or query: OR (the AND operator in SQL has a higher priority than the OR operator, and you need to use parentheses when you want to execute the OR operator first)

select name from object_227xW__c where (field_rzv5M__c is null or field_rzv5M__c <= 100) order by last_modified_time desc limit 10 offset 0;

• Judge equality and inequality: =, !=

select _id, field_rzv5M__c, name from object_227xW__c where field_oc43W__c = '13988523405' limit 10 offset 0;
select _id, field_rzv5M__c, name from object_227xW__c where field_oc43W__c != '13988523405' limit 10 offset 0;
select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c = 100 limit 10 offset 0;
select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c != 100 limit 10 offset 0;

• greater than: >, less than: <

select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c > 100 limit 10 offset 0;
select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c < 100 limit 10 offset 0;

• greater than or equal to: >=, less than or equal to: =<

select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c >= 100 limit 10 offset 0;
select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c <= 100 limit 10 offset 0;

• Fuzzy query: like, not like, when like 'xx' does not put %, it is equivalent to =

select _id, field_rzv5M__c, name from object_227xW__c where field_oc43W__c like '%88523%';
select _id, field_rzv5M__c, name from object_227xW__c where field_oc43W__c like '%88523';
select _id, field_rzv5M__c, name from object_227xW__c where field_oc43W__c like '88523%';
select _id, field_rzv5M__c, name from object_227xW__c where field_oc43W__c not like '%88523%';

• Null value judgment: is null, is not null

select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c is null;
select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c is not null;

• The array contains: @>, applicable to fields whose data type is List, such as unit, multi-choice, personnel, department, etc.

select _id, field_D8JyW__c, name from object_227xW__c where field_D8JyW__c @> ARRAY['option1', 'option2'];

• in statement: IN, NOT IN

select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c IN (21, 100);
select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c not IN (21, 100);

• between statement: BETWEEN AND, NOT BETWEEN AND

select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c BETWEEN 21 and 100;
select _id, field_rzv5M__c, name from object_227xW__c where field_rzv5M__c not BETWEEN 21 and 100;

• Supports comparison between fields, does not support in, between, @> and other operators

select name from AccountObj where last_modified_time > create_time;
select name from AccountObj where name != firstName;
select name from AccountObj where name = firstName;
select name from AccountObj where name <= firstName;

4. Code case

Groovy:

String sql = "select name from object_227xW__c"

SelectAttribute att = SelectAttribute. builder()
  .needCount(true)
  .build()

def (Boolean error, QueryResult queryResult, String message) = Fx. object. select(sql, att)
if (error) {
  log.info("select error: " + message)
} else {
  log. info(queryResult)
}
List dataList = queryResult.dataList;
dataList. each { it->
  Map map = (Map) it
  log.info(map['field_rzv5M__c'])
}