nodejs sequelize 多表分页查询，返回结果包含两表字段寻技术

goods和goodsSkus表

实现两表联表查询，并分页。查询结果中包含两表中的字段，重点在于子表中返回字段要写在include中的attributes中，否则生成的sql无法执行

let queryParams={
　　name: {
　　　　[sequelize.Op.like]: `%$测试%`
　　}
}

let skuQueryParams = {
　　name: {
　　　　[sequelize.Op.like]: `%$x%`
　　}
}

daGoodsModel.hasMany(daGoodsSkuModel, {foreignKey: 'goods_id'});

var results = await daGoodsModel.findAll({

order: [['id','asc']],

where: queryParams,

raw: true,

include: [

{

model: daGoodsSkuModel,

attributes:[

[sequelize.col('id'), 'sku_id'],

[sequelize.col('name'), 'sku_name']

where: skuQueryParams,

require: true

}

attributes: [

'id','name'

limit: pageSize,

offset: (pageIndex - 1) * pageSize

});

sku表返回字段放到include.attributes中生成的sql

SELECT `da_goods`.*, `da_goods_skus`.`id` AS `da_goods_skus.sku_id`, `da_goods_skus`.`name` AS `da_goods_skus.sku_name`
FROM (
	SELECT `da_goods`.`id`, `da_goods`.`name`
	FROM `da_goods` `da_goods`
	WHERE ((`da_goods`.`name` LIKE '%测试%'
			OR `da_goods`.`subtitle` LIKE '%测试%')
		AND `da_goods`.`deleted` = false
		AND (
			SELECT `goods_id`
			FROM `da_goods_sku` `da_goods_skus`
			WHERE `da_goods_skus`.`deleted` = false
				AND `da_goods_skus`.`name` LIKE '%x%'
				AND `da_goods_skus`.`goods_id` = `da_goods`.`id`
			LIMIT 1
		) IS NOT NULL)
	ORDER BY `da_goods`.`id` ASC
	LIMIT 0, 10
) `da_goods`
	INNER JOIN `da_goods_sku` `da_goods_skus`
	ON (`da_goods`.`id` = `da_goods_skus`.`goods_id`
		AND `da_goods_skus`.`deleted` = false
		AND `da_goods_skus`.`name` LIKE '%x%')
ORDER BY `da_goods`.`id` ASC;

sku返回字段放到attributes中

var results = await daGoodsModel.findAll({
	order:      [['id','asc']],
	where:      queryParams,
	raw:        true,
	include:  [
		{
		    model: daGoodsSkuModel, 
		    attributes:[],
		    where: skuQueryParams,
		    require: true
		}
	],
	attributes:  [
            'id','name',
            [sequelize.col('da_goods_skus.id'), 'sku_id'],
            [sequelize.col('da_goods_skus.name'), 'sku_name']
	],
	limit:  pageSize,
	offset: (pageIndex - 1) * pageSize
});

　生成的sql语句，如下语句执行失败，会报 "Unknown column 'da_goods_skus.id' in 'field list'"，因为标红部分查询不到

SELECT `da_goods`.*
FROM (
    SELECT `da_goods`.`id`, `da_goods`.`name`, `da_goods_skus`.`id` AS `sku_id`, `da_goods_skus`.`name` AS `sku_name`
    FROM `da_goods` `da_goods`
    WHERE ((`da_goods`.`name` LIKE '%测试%'
            OR `da_goods`.`subtitle` LIKE '%测试%')
        AND `da_goods`.`deleted` = false
        AND (
            SELECT `goods_id`
            FROM `da_goods_sku` `da_goods_skus`
            WHERE `da_goods_skus`.`deleted` = false
                AND `da_goods_skus`.`name` LIKE '%x%'
                AND `da_goods_skus`.`goods_id` = `da_goods`.`id`
            LIMIT 
        ) IS NOT NULL)
    ORDER BY `da_goods`.`id` ASC
    LIMIT , 10
) `da_goods`
    INNER JOIN `da_goods_sku` `da_goods_skus`
    ON (`da_goods`.`id` = `da_goods_skus`.`goods_id`
        AND `da_goods_skus`.`deleted` = false
        AND `da_goods_skus`.`name` LIKE '%x%')
ORDER BY `da_goods`.`id` ASC;

正确返回结果，需要对返回字段进行处理，去掉 da_goods_skus

[
	{
		"id": 334,
		"name": "测试商品",
		"da_goods_skus.sku_id": 506,
		"da_goods_skus.sku_name": "xl"
	},
	{
		"id": 334,
		"name": "测试商品",
		"da_goods_skus.sku_id": 507,
		"da_goods_skus.sku_name": "x"
	}
]

但是上面node生成sql两层嵌套，总感觉有问题，并且分页查询时如果与goods对应的sku表有多条数据，会返回所有sku，经查是sequelize对sql进行了优化，添加 subQuery:false,即可

代码

var results = await daGoodsModel.findAll({
    order:      [['id','asc']],
    subQuery:   false,
    where:      queryParams,
    include:  [
        {
            model: daGoodsSkuModel, 
            attributes:[],
            where: skuQueryParams
        }
    ],
    attributes:  [
        'id','name',
        [sequelize.col('da_goods_skus.id'), 'sku_id'],
        [sequelize.col('da_goods_skus.name'), 'sku_name'],
    ],
    limit:  pageSize,
    offset: (pageIndex - 1) * pageSize
});

生成SQL

SELECT `da_goods`.`id`, `da_goods`.`name`, `da_goods_skus`.`id` AS `sku_id`, `da_goods_skus`.`name` AS `sku_name`
FROM `da_goods` `da_goods`
    INNER JOIN `da_goods_sku` `da_goods_skus`
    ON `da_goods`.`id` = `da_goods_skus`.`goods_id`
        AND `da_goods_skus`.`deleted` = false
WHERE `da_goods`.`deleted` = false
ORDER BY `da_goods`.`id` ASC
LIMIT , ;

但是这样返回的sku永远只有一条，也就是goods有几条，返回几条记录，对应的sku只返回一条

[{
    "id": 334,
    "name": "测试商品",
    "sku_id": 506,
    "sku_name": "xl",
},
{
    "id": 335,
    "name": "测试1",
    "sku_id": 508,
    "sku_name": "aaa",
}]

还是有问题

将sku返回字段放到include.attributes 解决问题

var results = await daGoodsModel.findAndCountAll({
    order:      [['id','asc'],[sequelize.col('da_goods_skus.id'),'asc']],
    subQuery:   false,
    where:      queryParams,
    include:  [
        {
            model: daGoodsSkuModel, 
            attributes:[                        
                [sequelize.col('id'), 'sku_id'],
                [sequelize.col('name'), 'sku_name']
            ],
            where: skuQueryParams
        }
    ],
    attributes:  [
        'id','name'
    ],
    limit:  pageSize,
    offset: (pageIndex - 1) * pageSize
});

返回结果

[{
    "id": 334,
    "name": "测试商品",
    "da_goods_skus": [
        {
            "sku_id": 506,
            "sku_name": "xl"
        },
        {
            "sku_id": 507,
            "sku_name": "x"
        }
    ],
},
{
    "id": 335,
    "name": "测试1",
    "da_goods_skus": [
        {
            "sku_id": 508,
            "sku_name": "aaa"
        }
    ]
}]

https://blog.zeromake.com/pages/sequelize-trap/

本文地址：https://www.xunjs.com/javascript/view_10653.html

Tags：表字 Sequelize 分页查询包含 NodeJS 字段结果返回

相关推荐