FQL

1. 什么是FQL

• FQL是能够在APL里使用的类SQL查询语句，开发人员能够在APL中使用FQL查询企业中的业务数据

2. FQL语法

• 目前支持的语法:

  SELECT
      字段名1 [, 字段名2 ...]
      [FROM 对象apiName
      [WHERE where_condition]
      [ORDER BY {col_name | expr | position}
        [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  

  注意项

  • 不支持select *查询所有字段，只能通过select field1, field2...按需查找相关字段
  • limit默认为10，最大值为100，offset默认为0
  • 使用OR条件需要单独开通，请联系销售人员下订单开通产品：【对象列表筛选支持或】

3.Where条件支持的操作符

• 与查询：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（SQL中AND运算符的优先级高于OR运算符，想要优先执行OR运算符时，需要使用括号）

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;

• 判断相等和不相等：=，!=

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;

• 大于：>，小于：<

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;

• 大于或等于：>=，小于或等于：=<

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;

• 模糊查询：like，not like，当 like 'xx' 没有放 %时相当于=

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值判断：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;

• 数组包含：@>，适用于单元、多选、人员、部门等数据类型为List的字段

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

• in语句：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语句：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;

• 支持字段间的比较，不支持in，between，@>等操作符

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.代码案例

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'])
}