简介
FlexibleSearch 框架自动解析类型和数据库表依赖关系，不需要指定显式的数据库表名，类型系统和数据库表示之间的整个转换过程是自动进行的，并在必要时使用 UNION 和 JOIN 生成数据库可执行的 SQL 语句。
SELECT
{p:pk},{p.code},{p.name[zh]}
FROM {Product as p}
SELECT
item_t0.PK, item_t0.p_code, lp_t0.p_name
FROM products item_t0
JOIN productslp lp_t0 ON item_t0.PK = lp_t0.ITEMPK
FlexibleSearch 查询的基本语法
SELECT <selects> FROM <types> ( WHERE <conditions> )? ( ORDER BY <order> )?
<selects> 返回字段（数据库列）
- 获取所有字段
-
{*}按 SQL 约定返回所有数据库列，例如：SELECT {*} FROM {Product}。
-
- 获取指定字段
-
{attribute}指定属性标识符用必须使用{}包裹，例如：SELECT {code} FROM {Product}。
-
- 获取国际化字段
-
{attribute[zh]}使用语言标识符作为属性名称中的后缀，并用[]包裹，例如SELECT {name[zh]}, {name[en]} FROM {Product}。
-
<types> 对象模型 类型 code
-
FROM子句中的<types>字段的值需要是itemtype的code属性，FROM子句必须用{}包裹 - 可以使用
AS给类型指定别名，例如SELECT {p.code} FROM {Product AS p} ORDER BY {p.code} - 可以使用
JOIN和LEFT JOIN联合查询，例如SELECT {c.code} 配货单号, {cs.code} 配货单状态 FROM { Consignment as c JOIN ConsignmentStatus as cs ON {c.status} = {cs.pk} - 子类型：默认查询搜索该类型和它的子类型所有数据，需要在
{}中的类型后缀添加!，指定不查询子类型- 查询子类型例子：
SELECT {code},{pk} FROM {Product} - 不查询子类型例子：
SELECT {code},{pk} FROM {Product!}
- 查询子类型例子：
条件
| 序号 | SQL命令/关键字 | 说明 | 代码示例 |
|---|---|---|---|
| 1 | ORDER BY {alias:attribute} | 按指定属性值排序（默认ASC） | SELECT {p:pk},{p.creationtime},{p.modifiedtime} FROM {Product as p} ORDER BY {p:creationtime} |
| 2 | ASC |
按升序对结果进行排序（a ... z，0 ... 9） | SELECT {p:pk},{p.creationtime},{p.modifiedtime} FROM {Product as p} ORDER BY {p:creationtime} ASC |
| 3 | DESC |
按降序对结果进行排序（z ... a，9 ... 0） | SELECT {p:pk},{p.creationtime},{p.modifiedtime} FROM {Product as p} ORDER BY {p:creationtime} DESC |
| 4 | DISTINCT |
消除查询结果中的重复项 | SELECT DISTINCT({p.creationtime}),{p:pk},{p.modifiedtime} FROM {Product as p} |
| 5 | OR |
或者 | SELECT {p:pk},{p.creationtime},{p.modifiedtime} FROM {Product as p} WHERE {p.creationtime} >= '2019-03-30 21:07:51' OR {p.modifiedtime} >= '2019-03-30 21:07:50' |
| 6 | AND |
并且 | SELECT {p:pk},{p.creationtime},{p.modifiedtime} FROM {Product as p} WHERE {p.creationtime} >= '2019-03-30 21:07:51' AND {p.modifiedtime} >= '2019-03-30 21:07:50' |
| 7 | IS [NOT] NULL |
返回是null/非null结果 | ... WHERE ... IS [NOT] NULL |
| 8 | [NOT] IN |
属于/不属于 | ... WHERE ... [NOT] IN (... , ...) |
| 9 | [NOT] EXISTS |
存在/不存在 | SELECT {o.code}, FROM {order as o} WHERE 1=1 AND NOT EXISTS({{ SELECT {c.PK} FROM {Consignment as c} WHERE {c.order}={o.pk} }}) |
| 10 | LIKE |
如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的 | ... WHERE ... LIKE '...' |
| 11 | % |
匹配任意数量字符的通配符 | ... WHERE ... LIKE '%...' , '...%...' , '...%' , '%...%' |
| 12 | _ |
匹配单个字符的通配符 | ... WHERE ... LIKE '_...' , '..._...' , '..._' |
| 13 | LEFT JOIN ON |
左链接，左表返回符合条件的所有行，右表没有的数据显示null | SELECT {c.code} 配货单号, {cs.code} 配货单状态 FROM { Consignment as c JOIN ConsignmentStatus as cs ON {c.status} = {cs.pk} |
| 14 | = 、 !=, <> 、 < 、 <= 、 > 、 >= |
||
| 15 | CONCAT |
连接两个结果 - 右侧的示例将返回字符串结果 | SELECT concat({c.code},'-',{cs.code}) as '配货单号-配货单状态' FROM { Consignment as c JOIN ConsignmentStatus as cs ON {c.status} = {cs.pk} } WHERE {cs.code} = 'APPROVED' |
| 16 | :o |
SELECT count({p:pk}) FROM {Product AS p} WHERE {p:name[zh]} LIKE '%套头无帽卫衣%' |
SELECT count(lp_t0.ITEMPK) FROM productslp lp_t0 WHERE lp_t0.p_name LIKE '%套头无帽卫衣%' |
| 17 | :o |
SELECT count({p:pk}) FROM {Product AS p} WHERE {p:name[zh]:o} LIKE '%套头无帽卫衣%' |
SELECT count(item_t0.PK) FROM products item_t0 LEFT JOIN productslp lp_t0 ON item_t0.PK = lp_t0.ITEMPK WHERE lp_t0.p_name LIKE '%套头无帽卫衣%' |
| 18 | :o |
SELECT count({p:pk}) FROM {Product AS p} WHERE {p:name[zh]} LIKE '%套头无帽卫衣%' OR {p:name[en]} LIKE '%套头无帽卫衣%' |
SELECT count(lp_t0.ITEMPK) FROM productslp lp_t0 JOIN productslp lp_t0_l1 ON lp_t0.ITEMPK = lp_t0_l1.ITEMPK WHERE lp_t0.p_name LIKE '%套头无帽卫衣%' OR lp_t0_l1.p_name LIKE '%套头无帽卫衣%' |
| 19 | :o |
SELECT count({p:pk}) FROM {Product AS p} WHERE {p:name[zh]:o} LIKE '%套头无帽卫衣%' OR {p:name[en]:o} LIKE '%套头无帽卫衣%' |
SELECT count(item_t0.PK) FROM products item_t0 LEFT JOIN productslp lp_t0 ON item_t0.PK = lp_t0.ITEMPK LEFT JOIN productslp lp_t0_l1 ON item_t0.PK = lp_t0_l1.ITEMPK WHERE lp_t0.p_name LIKE '%套头无帽卫衣%' OR lp_t0_l1.p_name LIKE '%套头无帽卫衣%' |
| 21 | {locAttr[ANY]} |
所有国际化定义 | SELECT count(DISTINCT {p:PK}) FROM {Product AS p} WHERE {p:name[ANY]} LIKE '%套头无帽卫衣%' |
| 22 | Product! |
ModelType加!FlexibleSearch查询仅搜索指定的类型并省略所有子类型 |
SELECT {code},{pk} FROM {Product!} |
