代码
const group = await Cabinet.findAll({
include: [tagsInfo],
where: opt,
attributes: ["groupName", "type"],
group: ["groupName", "type"],
logging: console.log,
});
生成的查询语句
SELECT “Cabinet”.“id”, “Cabinet”.“groupName”, “Cabinet”.“type”, “tags”.“id” AS “tags.id”, “tags”.“name” AS “tags.name”, “tags”.“type” AS “tags.type”, “tags”.“pid” AS “tags.pid”, “tags->cabinet_tags_map”.“createdAt” AS “tags.cabinet_tags_map.createdAt”, “tags->cabinet_tags_map”.“updatedAt” AS “tags.cabinet_tags_map.updatedAt”, “tags->cabinet_tags_map”.“TagId” AS “tags.cabinet_tags_map.TagId”, “tags->cabinet_tags_map”.“CabinetId” AS “tags.cabinet_tags_map.CabinetId” FROM “cabinet_cabinet” AS “Cabinet” LEFT OUTER JOIN ( “cabinet_tags_map” AS “tags->cabinet_tags_map” INNER JOIN “cabinet_tags” AS “tags” ON “tags”.“id” = “tags->cabinet_tags_map”.“TagId”) ON “Cabinet”.“id” = “tags->cabinet_tags_map”.“CabinetId” AND (“tags”.“deletedAt” > ‘2019-02-13 16:33:33.971 +08:00’ OR “tags”.“deletedAt” IS NULL) WHERE (“Cabinet”.“deletedAt” > ‘2019-02-13 16:33:33.971 +08:00’ OR “Cabinet”.“deletedAt” IS NULL) GROUP BY “groupName”, “type”;
问题
查询语句第一行中的select中 除了我需要查询的字段 groupName , type 之外多出了很多无用的字段,这些导致报错
column "Cabinet.id" must appear in the GROUP BY clause or be used in an aggregate function
这有没有办法去除?
如果可以的话希望大佬们能更改下代码,我去研究研究
帮顶一下。 orm 的话还是推荐使用 typeorm
可以把tagsInfo里的attributes和through里的attributes设为[],但是看你打印出来的sql,关联表用的外连接,tagsInfo是用来干啥的?需求是啥
@supperchong 好的,我有空试一下,现在直接用knexjs去生成sql语句查询了。
使用through
加个:raw:true
在表连接的include里定义连接表要查询的字段,不定义默认查询全部