sequelize 使用 find&findAll查询时,能否去除多余查询的列?
发布于 6 年前 作者 91hanbao 3742 次浏览 来自 问答
代码
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 这有没有办法去除? 如果可以的话希望大佬们能更改下代码,我去研究研究

6 回复

帮顶一下。 orm 的话还是推荐使用 typeorm

可以把tagsInfo里的attributes和through里的attributes设为[],但是看你打印出来的sql,关联表用的外连接,tagsInfo是用来干啥的?需求是啥

@supperchong 好的,我有空试一下,现在直接用knexjs去生成sql语句查询了。

加个:raw:true

在表连接的include里定义连接表要查询的字段,不定义默认查询全部

回到顶部