[Bug]: 在GetXXXXXInfoList里面使用join操作并且是指定的select的字段时，select没生效

[bingochen87]

gin-vue-admin 版本

master

Node 版本

v14.16.0

Golang 版本

go 1.17

是否依旧存在

可以

bug描述

在GetXXXXXInfoList里面使用join操作并且是指定的select的字段时，select没生效

例如

    db = db.Select("`user`.*, `account`.balance").
        Joins("left join `account` on `account`.user_id = `user`.user_id")

在现有的情况下，是直接是查询user表的信息，没加入account.balance这个字段。

修改建议

修复方法是将

    err = db.Count(&total).Error

这一句，移到

    err = db.Limit(limit).Offset(offset).Scan(&users).Error

的后面

    err = db.Limit(limit).Offset(offset).Scan(&users).Error
    if err!=nil {
	return
    }

    err = db.Count(&total).Error

[songzhibin97]

没看懂,源码有问题麻烦提供文件行号

[bingochen87]

没看懂,源码有问题麻烦提供文件行号

用生成的"service/autocode"里面的随便一个文件的GetXXXXInfoList这个方法就可以测试出来。

[bingochen87]

以example的customer代码为例，加入customerExt这个表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for gva_customer_ext
-- ----------------------------
DROP TABLE IF EXISTS `gva_customer_ext`;
CREATE TABLE `gva_customer_ext` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `c_id` bigint DEFAULT NULL,
  `avatar` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_gva_customer_ext_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SET FOREIGN_KEY_CHECKS = 1;

复现方法

修改server/service/example/exa_customer.go 64行开始的GetCustomerInfoList方法，修改为

func (exa *CustomerService) GetCustomerInfoList(sysUserAuthorityID string, info request.PageInfo) (err error, list interface{}, total int64) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&example.ExaCustomer{}).
		Select("`exa_customers`.*, `gva_customer_ext`.avatar").
		Joins("left join `gva_customer_ext` on `gva_customer_ext`.c_id = `exa_customers`.id")
	var a system.SysAuthority
	a.AuthorityId = sysUserAuthorityID
	err, auth := systemService.AuthorityServiceApp.GetAuthorityInfo(a)
	if err != nil {
		return
	}
	var dataId []string
	for _, v := range auth.DataAuthorityId {
		dataId = append(dataId, v.AuthorityId)
	}
	var CustomerList []example.ExaCustomer
	err = db.Where("sys_user_authority_id in ?", dataId).Count(&total).Error
	if err != nil {
		return err, CustomerList, total
	} else {
		err = db.Limit(limit).Offset(offset).Preload("SysUser").Where("sys_user_authority_id in ?", dataId).Find(&CustomerList).Error
	}
	return err, CustomerList, total
}

修改完后重新运行，访问 http://localhost:8080/#/layout/example/customer，这里会得到这样的sql

SELECT `exa_customers`.`id`,`exa_customers`.`created_at`,`exa_customers`.`updated_at`,`exa_customers`.`deleted_at`,`exa_customers`.`customer_name`,`exa_customers`.`customer_phone_data`,`exa_customers`.`sys_user_id`,`exa_customers`.`sys_user_authority_id` FROM `exa_customers` left join `gva_customer_ext` on `gva_customer_ext`.c_id = `exa_customers`.id WHERE (sys_user_authority_id in ("888","8881","9528")) AND `exa_customers`.`deleted_at` IS NULL AND (sys_user_authority_id in ("888","8881","9528")) LIMIT 10

里面只取了exa_customers 的所有字段，期望的是需要将exa_customers的所有字段及gva_customer_ext的avatar字段取出。

暂时的修复方法

因为我还没仔细看具体的原因，只是尝试一下注释掉count 的操作或者是将count操作移到find的后面就正常了（没尝试深拷贝，将count和find的操作db分离）

func (exa *CustomerService) GetCustomerInfoList(sysUserAuthorityID string, info request.PageInfo) (err error, list interface{}, total int64) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&example.ExaCustomer{}).
		Select("`exa_customers`.*, `gva_customer_ext`.avatar").
		Joins("left join `gva_customer_ext` on `gva_customer_ext`.c_id = `exa_customers`.id")
	var a system.SysAuthority
	a.AuthorityId = sysUserAuthorityID
	err, auth := systemService.AuthorityServiceApp.GetAuthorityInfo(a)
	if err != nil {
		return
	}
	var dataId []string
	for _, v := range auth.DataAuthorityId {
		dataId = append(dataId, v.AuthorityId)
	}
	var CustomerList []example.ExaCustomer
	if err != nil {
		return err, CustomerList, total
	} else {
		err = db.Limit(limit).Offset(offset).Preload("SysUser").Where("sys_user_authority_id in ?", dataId).Find(&CustomerList).Error
	}
	err = db.Where("sys_user_authority_id in ?", dataId).Count(&total).Error
	return err, CustomerList, total
}

这样就可以正确得到想要的sql了

SELECT `exa_customers`.*, `gva_customer_ext`.avatar FROM `exa_customers` left join `gva_customer_ext` on `gva_customer_ext`.c_id = `exa_customers`.id WHERE (sys_user_authority_id in ("888","8881","9528")) AND `exa_customers`.`deleted_at` IS NULL LIMIT 10

理论上，这个应该是算是gorm本身的bug，如果只是简单的修复，就是要count操作的模板移到find操作的后面，这样生成的代码就不会有问题。

[bingochen87]

修复方法有问题，将count放find后面，得到的total是0.

[bingochen87]

修复方法有问题，将count放find后面，得到的total是0.

因为是加入了limit和offset的处理，所以从第二页开始，取得的total数据为0

# 第二页
SELECT count(1) FROM `live` left join `user` on `user`.user_id = `live`.user_id WHERE `live`.`deleted_at` IS NULL LIMIT 10 OFFSET 10

# 第一页
SELECT count(1) FROM `live` left join `woo_user` on `user`.user_id = `live`.user_id WHERE `live`.`deleted_at` IS NULL LIMIT 10

[luokuncool]

我看 go.mod 里面现在用的是 gorm v1.20.7，我试了下的确可以复现这个问题。

第一种解决办法：

可以把 Select 方法调用调整到下方 Find 取数据那里。

func (exa *CustomerService) GetCustomerInfoList(sysUserAuthorityID string, info request.PageInfo) (err error, list interface{}, total int64) {
	limit := info.PageSize
	offset := info.PageSize * (info.Page - 1)
	db := global.GVA_DB.Model(&example.ExaCustomer{}).
		Joins("left join `gva_customer_ext` on `gva_customer_ext`.c_id = `exa_customers`.id")
	var a system.SysAuthority
	a.AuthorityId = sysUserAuthorityID
	err, auth := systemService.AuthorityServiceApp.GetAuthorityInfo(a)
	if err != nil {
		return
	}
	var dataId []string
	for _, v := range auth.DataAuthorityId {
		dataId = append(dataId, v.AuthorityId)
	}
	var CustomerList []example.ExaCustomer
	err = db.Where("sys_user_authority_id in ?", dataId).Count(&total).Error
	if err != nil {
		return err, CustomerList, total
	} else {
		err = db.Select("`exa_customers`.*, `gva_customer_ext`.avatar").Limit(limit).Offset(offset).Preload("SysUser").Where("sys_user_authority_id in ?", dataId).Find(&CustomerList).Error
	}
	return err, CustomerList, total
}

第二种办法：

把 gorm 升级到 v1.20.11 也可以正常。