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 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;

正确返回结果 ，需要对返回字段进行处理，去掉 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 0, 1;

但是这样返回的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/