sequlize 技巧

1. sequlize mysql 定义的时间类型为 datetime

直接插入时，由于未设置时区，导致在phpmyadmin直接看时，时间错误。

解决方法：在 new Sequelize 对象时，加入 timezone 选项。

具体参考资料 https://segmentfault.com/a/1190000004292140

 

2. findAndCountAll 问题 count  的计数问题

models.Album.findAndCountAll({
        offset: (page - 1 ) * pageSize,
        limit: pageSize,
        distinct: true,
        include: [
            {
                model: models.Product,
                as: 'products',
                required: true
            }
        ]
    }).then(result => {
        res.send({
            success: true,
            message: "查询成功",
            data: {
                albums: result
            }
        });
    }).catch(error => {
        console.log(req.originalUrl + " ---- " + error.stack);
        res.send({
            success: false,
            message: "该专题不存在"
        });
    })

 

findOptions 是否加入  distinct: true  的选项，会影响 最终sql 的生成

 

with  distinct: true   这个为主表的查询条数。

SELECT COUNT(DISTINCT `Album`.`id`) AS `count`
FROM `albums` `Album`
    INNER JOIN `album_product_mappings` `products.Album_product_mapping`
        INNER JOIN `products` `products` ON `products`.`id` = `products.Album_product_mapping`.`product_id` ON `Album`.`id` = `products.Album_product_mapping`.`album_id`;

----

without  distinct: true   这个是 主表关联从表后的条数

SELECT COUNT(`Album`.`id`) AS `count`
FROM `albums` `Album`
    INNER JOIN `album_product_mappings` `products.Album_product_mapping`
        INNER JOIN `products` `products` ON `products`.`id` = `products.Album_product_mapping`.`product_id` ON `Album`.`id` = `products.Album_product_mapping`.`album_id`;

 

3. N:M 问题，忽略中间表的返回

n:m 的中间表通常保存着一些附加信息。不过有时候，只是保存着这个关联关系，如果返回的 Object 中也包含这个信息的话，直接return到前端的话，就有点小浪费带宽了。可以通过select的时候，在 include 中加入  through: {attributes: []}  避免其加入到返回的Object中。（需注意的是，有无  through: {attributes: []} ，生成的Sql 都是一样的，只是，不将其加入到返回的 result 中）

具体代码：

models.Album.findAndCountAll({
        offset: (page - 1 ) * pageSize,
        limit: pageSize,
        distinct: true,
        include: [
            {
                model: models.Product,
                attributes: ["imgs", "id"],
                as: 'products',
                required: true,
                through: {attributes: []}
            }
        ]
    }).then(result => {
        res.send({
            success: true,
            message: "查询成功",
            data: {
                albums: result
            }
        });
    }).catch(error => {
          // do other something.
    })

 

再者：  required: true  有无生成的SQL， 区别在于 INNER JOIN ( required: true )还是 LEFT OUTER JOIN (required: false)

有：

SELECT 
  `Album`.*, 
  `products`.`imgs` AS `products.imgs`, 
  `products`.`id` AS `products.id`, 
  `products.Album_product_mapping`.`album_id` AS `products.Album_product_mapping.album_id`, 
  `products.Album_product_mapping`.`product_id` AS `products.Album_product_mapping.product_id`, 
  `products.Album_product_mapping`.`sort` AS `products.Album_product_mapping.sort`, 
  `products.Album_product_mapping`.`created_at` AS `products.Album_product_mapping.created_at`, 
  `products.Album_product_mapping`.`updated_at` AS `products.Album_product_mapping.updated_at` 
FROM 
  (
    SELECT 
      `Album`.`id`, 
      `Album`.`name`, 
      `Album`.`views`, 
      `Album`.`sort`, 
      `Album`.`status`, 
      `Album`.`created_at`, 
      `Album`.`updated_at` 
    FROM 
      `albums` AS `Album` 
    LIMIT 
      0, 20
  ) AS `Album` 
  INNER JOIN (
    `album_product_mappings` AS `products.Album_product_mapping` 
    INNER JOIN `products` AS `products` ON `products`.`id` = `products.Album_product_mapping`.`product_id`
  ) ON `Album`.`id` = `products.Album_product_mapping`.`album_id`;

 

无：

SELECT 
  `Album`.*, 
  `products`.`imgs` AS `products.imgs`, 
  `products`.`id` AS `products.id`, 
  `products.Album_product_mapping`.`album_id` AS `products.Album_product_mapping.album_id`, 
  `products.Album_product_mapping`.`product_id` AS `products.Album_product_mapping.product_id`, 
  `products.Album_product_mapping`.`sort` AS `products.Album_product_mapping.sort`, 
  `products.Album_product_mapping`.`created_at` AS `products.Album_product_mapping.created_at`, 
  `products.Album_product_mapping`.`updated_at` AS `products.Album_product_mapping.updated_at` 
FROM 
  (
    SELECT 
      `Album`.`id`, 
      `Album`.`name`, 
      `Album`.`views`, 
      `Album`.`sort`, 
      `Album`.`status`, 
      `Album`.`created_at`, 
      `Album`.`updated_at` 
    FROM 
      `albums` AS `Album` 
    LIMIT 
      0, 20
  ) AS `Album` 
  LEFT OUTER JOIN (
    `album_product_mappings` AS `products.Album_product_mapping` 
    INNER JOIN `products` AS `products` ON `products`.`id` = `products.Album_product_mapping`.`product_id`
  ) ON `Album`.`id` = `products.Album_product_mapping`.`album_id`;

 

转载于:https://www.cnblogs.com/au_ww/p/7054715.html