|
本帖最后由 yonyouhmm 于 2024-1-15 12:07 编辑
一、说明
yonql支持查询自建应用的实体,也支持查询其他各领域单据的实体。如果在自建应用中查询自建实体,即同领域中查询(公有云标准版应用构建自建应用领域domainKey都是developplatform,是同一个领域),使用ObjectStore.queryByYonQL(sql)即可;如果是跨领域查询,使用ObjectStore.queryByYonQL(sql,"实体所属领域"),第二个参数必填。我们在此案例汇总yonql编写的所有场景,并且会持续维护。
简单查询
主子关联查询
非主子关联
关键字、比较符、函数
二、相关知识
领域实体的uri和domainKey如何获取?
从“业务对象”菜单获取,以采购订单为例
三、yonql查询实践
在这里,使用请购单和采购订单为例,做一条请购单,采购订单拉单请购单生成,子表各两条数据。
在自建应用中编写后端函数,进行yonql查询。
1、简单查询-采购订单
- let sql = "select id,code from pu.purchaseorder.PurchaseOrder where code='PO240104-002'";
- var res = ObjectStore.queryByYonQL(sql);
复制代码 报错:
You have an error in your SQL syntax;。querySchema:{\"fullname\":\"pu.purchaseorder.PurchaseOrder\",\"fields\":[{\"name\":\"id\",\"alias\":\"id\"},{\"name\":\"code\",\"alias\":\"code\"}],\"conditions\":[{\"op\":\"and\",\"items\":[{\"name\":\"code\",\"op\":\"eq\",\"v1\":\"PO240104-002\",\"attr\":\"pu.purchaseorder.PurchaseOrder.code\"},{\"name\":\"tenant\",\"op\":\"eq\",\"v1\":3254158838717200,\"attr\":\"pu.purchaseorder.PurchaseOrder.tenant\"}]}],\"pager\":{\"pageIndex\":0,\"pageSize\":5000,\"from\":0,\"to\":0}}"
原因是自建应用里查询其他领域实体,没有添加domainKey。
正确使用:
- let sql = "select id,code from pu.purchaseorder.PurchaseOrder where code='PO240104-002'";
- var res = ObjectStore.queryByYonQL(sql,'upu');
复制代码 2、主子关联查询-采购订单
形态一:以子表维度展示数据
方式A:
主表没有别名;
子表有别名;
子表的mainid是主表的外键,可以从实体中查看
- let sql = "select id,code,t.product,t**.**ame as productName, t.qty from pu.purchaseorder.PurchaseOrder left join pu.purchaseorder.PurchaseOrders t on id=t.mainid where code='PO240104-002'"
- var res = ObjectStore.queryByYonQL(sql,'upu');
复制代码 返回数组中有两条数据,是以子表维度展示的。
- [{
- "code": "PO240104-002",
- "t_qty": 5,
- "id": "1899722662379257856",
- "t_product": "1735837585225809924",
- "productName": "牛黄"
- }, {
- "code": "PO240104-002",
- "t_qty": 3,
- "id": "1899722662379257856",
- "t_product": "1735837971772866565",
- "productName": "麝香"
- }]
复制代码
方式B:
查询子表,通过子表中的mainid直接穿透取值
另外客开经常有穿透查询参照其他字段的场景,直接字段编码.参照其他字段即可,比如下面的[size=2**.**ame
- let sql = "select product**.**ame as productName,qty,mainid.code as code from pu.purchaseorder.PurchaseOrders where mainid.code='PO240104-002'"
- var res = ObjectStore.queryByYonQL(sql,'upu');
复制代码
形态二:以主表维度展示数据
purchaseOrders 是子表集合属性,可以从ui模板中获取,也可以从detail请求中获取
- let sql = "select id,code,(select product**.**ame as productName,qty from purchaseOrders) as orders from pu.purchaseorder.PurchaseOrder where code='PO240104-002'"
- var res = ObjectStore.queryByYonQL(sql,'upu');
复制代码 返回数组中一条数据,以主表维度展示
[{
"code": "PO240104-002",
"orders": [{
"product": "1735837585225809924",
"qty": 5,
"mainid": "1899722662379257856",
"productName": "牛黄"
}, {
"product": "1735837971772866565",
"qty": 3,
"mainid": "1899722662379257856",
"productName": "麝香"
}
],
"id": "1899722662379257856"
}
]
3、非主子关系关联查询-采购订单关联请购单
能这样关联查询的两个实体需要属于同一个服务域
主表关联:
pu.purchaseorder.PurchaseOrder:采购订单
pu.applyorder.ApplyOrder:请购单
- let sql = "select id,code,t.code as applyCode from pu.purchaseorder.PurchaseOrder left join pu.applyorder.ApplyOrder t on srcBill = t.id where code='PO240104-002'"
- var res = ObjectStore.queryByYonQL(sql,'upu');
复制代码 结果:[{"code":"PO240104-002","id":"1899722662379257856","applyCode":"000040"}]
主子关联:
pu.purchaseorder.PurchaseOrder:采购订单
pu.purchaseorder.PurchaseOrders:采购订单子表
pu.applyorder.ApplyOrder:请购单
pu.applyorder.ApplyOrders:请购单子表
- let sql = "select id,code,t1.product,t1**.**ame as productName,t2.code as applyCode,t3.product as applyProduct from pu.purchaseorder.PurchaseOrder left join pu.purchaseorder.PurchaseOrders t1 on id=t1.mainid left join pu.applyorder.ApplyOrder t2 on srcBill = t2.id left join pu.applyorder.ApplyOrders t3 on t2.id = t3.mainid where code='PO240104-002'"
- var res = ObjectStore.queryByYonQL(sql,'upu');
复制代码 结果:
[
{"t1_product":"1735837585225809924","code":"PO240104-002","applyProduct":"1735837585225809924","id":"1899722662379257856","applyCode":"000040","productName":"牛黄"},{"t1_product":"1735837971772866565","code":"PO240104-002","applyProduct":"1735837585225809924","id":"1899722662379257856","applyCode":"000040","productName":"麝香"},{"t1_product":"1735837585225809924","code":"PO240104-002","applyProduct":"1735837971772866565","id":"1899722662379257856","applyCode":"000040","productName":"牛黄"},{"t1_product":"1735837971772866565","code":"PO240104-002","applyProduct":"1735837971772866565","id":"1899722662379257856","applyCode":"000040","productName":"麝香"}
]
4、主子孙关联查询
主表:AT18EE15E23EB8000A.AT18EE15E23EB8000A.grandPrent
子表:AT18EE15E23EB8000A.AT18EE15E23EB8000A.grandChild
孙表:AT18EE15E23EB8000A.AT18EE15E23EB8000A.grandSun
1、主表维度
let sql = "select id,new1,new2,(select id,new1,(select id,new1 from grandSunList) as grandSunList from grandChildList) as grandChildList from AT18EE15E23EB8000A.AT18EE15E23EB8000A.grandPrent";
var res = ObjectStore.queryByYonQL(sql);
2、孙表维度
let sql = "select id,a.id,b.id from AT18EE15E23EB8000A.AT18EE15E23EB8000A.grandSun left join AT18EE15E23EB8000A.AT18EE15E23EB8000A.grandChild a on grandChild_id=a.id left join AT18EE15E23EB8000A.AT18EE15E23EB8000A.grandPrent b on a.grandPrent_id=b.id";
var res = ObjectStore.queryByYonQL(sql);
三、关键字、比较符、函数
1、关键字
2、比较符
3、函数
|
本帖子中包含更多资源,您需要 登录 才可以下载或查看,没有帐号?立即注册
X
|