267 Star 1.9K Fork 539

GVPNocoBase / NocoBase

 / 详情

连接数据库视图功能,在跨表和使用count等组合的情况下,count列无法指定格式

待办的
创建于  
2024-02-20 13:41

开发者您好,
环境:
版本:nocobase:0.19.0-alpha.5,Node:v20.10.0,Yarn:1.22.21
操作系统:Alinos 23 x86_64(el8兼容),数据库:greatsql-8.0.32-25.1.el8(MySQL8兼容),

复现情况:
100%复现
现象
通过 数据表管理 --> 创建数据表 --> 连接数据库视图 时,视图部分列不支持选择字段类型,导致添加失败(未勾选 允许增删改操作)。
视图v1的创建语句为:
SELECT b.shiyongren as user,b.room,b.bumeng,a.MAC_Address,a.h ,a.IP_Address
FROM
(SELECT MAC_Address, COUNT()/6 as h ,IP_Address
FROM t_arpaistroy
WHERE (HOUR(createdAt)<1 OR HOUR(createdAt)>10 ) #UTC时间=CST-8
AND IP_Address LIKE '172%' #筛选客户端IP
GROUP BY MAC_Address,IP_Address) AS a
LEFT JOIN
(SELECT MAC_Address,shiyongren,room,bumeng
FROM djb) AS b
ON a.MAC_Address = b.MAC_Address
ORDER BY b.room DESC,b.bumeng DESC,
其中t_arpaistroy为通过nocobase建立,通过其他方法写入的表,djb为通过nocobase建立,通过nocobase添加和导入的表。通过连接数据库视图,其中h 既( COUNT(
)/6 )列无法选择 字段类型。
视图v1数据
视图v1数据
配置界面
配置界面
报错提示
报错提示

其他补充说明
数据表管理 --> 创建数据表 --> SQL数据表 时,功能正常

t_arpaistroy的ddl为:
CREATE TABLE t_arpaistroy (
createdAt datetime(3) NULL DEFAULT NULL,
updatedAt datetime(3) NULL DEFAULT NULL,
sort bigint NULL DEFAULT NULL,
createdById bigint NULL DEFAULT NULL,
updatedById bigint NULL DEFAULT NULL,
id bigint NOT NULL AUTO_INCREMENT,
IP_Address varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
MAC_Address varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
VLAN_ID varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
Interface varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
Aging varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
Type varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX t_arpaistroy_created_by_id(createdById ASC) USING BTREE,
INDEX t_arpaistroy_updated_by_id(updatedById ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

djb的ddl为:
CREATE TABLE djb (
createdAt datetime(3) NULL DEFAULT NULL,
updatedAt datetime(3) NULL DEFAULT NULL,
sort bigint NULL DEFAULT NULL,
createdById bigint NULL DEFAULT NULL,
updatedById bigint NULL DEFAULT NULL,
id bigint NOT NULL AUTO_INCREMENT,
room bigint NULL DEFAULT NULL,
BuMeng varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
ShiYongRen varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
shebeixinghao varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
yingpanhao varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
MAC_Address varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
chuchangshijian datetime(3) NULL DEFAULT NULL,
beizhu varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX djb_created_by_id(createdById ASC) USING BTREE,
INDEX djb_updated_by_id(updatedById ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 117 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

评论 (0)

CHS5747 创建了任务
CHS5747 修改了描述
吉宏波 添加了
 
bug
标签
展开全部操作日志

登录 后才可以发表评论

状态
负责人
里程碑
Pull Requests
关联的 Pull Requests 被合并后可能会关闭此 issue
分支
开始日期   -   截止日期
-
置顶选项
优先级
参与者(1)
TypeScript
1
https://gitee.com/nocobase/nocobase.git
git@gitee.com:nocobase/nocobase.git
nocobase
nocobase
NocoBase

搜索帮助