Node+MySQL+sequelizeで複数テーブル結合

Question

前提・実現したいこと

sequelizeのORMを使って、下記のSQL文を作成したいのですが、エラーとなり困っています。
どのように記述すべきであるかご助言いただけないでしょうか？

発生している問題・エラーメッセージ

SequelizeDatabaseError: Unknown column 'productId' in 'field list'

SequelizeDatabaseError: Column 'product_id' in field list is ambiguous

該当のソースコード

実行したいSQL文

select a.id as productId, a.product_name, c.src
 from product a
 ( select product_id, count(*) as sales_num
    from order_detail
   group by product_id
   order by sales_num desc
   limit 10
 ) b,
 mst_product_photo c
where a.id = b.product_id
 and  b.id = c.product_id
order by sales_num desc

■モデルの定義
productテーブル

const Sequelize = require('sequelize');

module.exports = (sequelize) => {
  const ProductModel = sequelize.define('product', {
    id       : { type: Sequelize.INTEGER(11).UNSIGNED.ZEROFILL , primaryKey: true, autoIncrement: true },
    productName : { field: 'product_name', type: Sequelize.STRING             },
  },
  {
    freezeTableName: true,
    timestamps: false
  });
  ProductModel.associate =  (Models) => {
    ProductModel.hasMany(Models.mstProductPhoto, {
      as: 'photoList',
      foreignKey: 'productId'
    });
    ProductModel.hasMany(Models.OrderDetailModel, {
      as: 'orderDetailList',
      foreignKey: 'productId'
    });
  };
  return ProductModel;
};

order_detailテーブル

const Sequelize = require('sequelize');

module.exports = (sequelize) => {
  const OrderDetailModel = sequelize.define('order_detail', {
    id: { type: Sequelize.INTEGER(11).UNSIGNED.ZEROFILL, primaryKey: true, autoIncrement: true },
    orderHeaderId: { field: 'order_header_id', type: Sequelize.INTEGER(11).UNSIGNED.ZEROFILL },
    productId: { field: 'product_id', type: Sequelize.INTEGER(11).UNSIGNED.ZEROFILL }
  },
  {
    freezeTableName: true,
    timestamps: false
  });
  OrderDetailModel.associate = (Models) => {
    OrderDetailModel.belongsTo(Models.Product, {
      foreignKey: 'productId',
      targetKey: 'id'
    });
  };
  return OrderDetailModel;
};

mst_product_photoテーブル

const Sequelize = require('sequelize');

module.exports = (sequelize) => {
  const mstProductPhotoModel = sequelize.define('mst_product_photo', {
    id       : { type: Sequelize.INTEGER(11) , primaryKey: true, autoIncrement: true },
    productId : { field: 'product_id', type: Sequelize.INTEGER(11).UNSIGNED.ZEROFILL },
    src : { type: Sequelize.STRING    }
  },
  {
    freezeTableName: true,
    timestamps: false
  });
  mstProductPhotoModel.associate =  (Models) => {
    mstProductPhotoModel.belongsTo(Models.Product, {
      foreignKey: 'product_id',
      targetKey: 'id'
    });
  };
  return mstProductPhotoModel;
};

SQL発行部分の処理（抜粋）

await Models.Product.findAll({
  attributes: [
    ['id', 'productId'],
    'productName'
  ],
  // order_detailテーブル結合
  include: [{
    model: Models.OrderDetailModel,
    as: 'orderDetailList',
    required: true,  // INNER JOIN
    attributes: [
      'productId',
      [sequelize.fn('count', sequelize.col('product_id')), 'sales_num']
    ],
    group: ['Models.OrderDetailModel.productId'],
    raw: true,
    order: sequelize.literal('sales_num DESC')
  },
  // mst_product_photoテーブル結合
  {
    model: Models.mstProductPhoto,
    as: 'photoList',
    required: true,  // INNER JOIN
    attributes: [
      ['id', 'productPhotoId'],
      'src'
    ]
  }]})

補足情報（FW/ツールのバージョンなど）

"sequelize": "^5.19.5",

よろしくお願いいたします。

Answer 1

抜粋で

 ( select product_id, count(*) as sales_num  -- ここで product_id, count(*) しか select していないから、
    from order_detail
   group by product_id
   order by sales_num desc
   limit 10
 ) b,
 mst_product_photo c
where a.id = b.product_id
 and  b.id = c.product_id  -- b.id がどこからも持ってくるのか不明です。

質問にテーブル定義をCREATE TABLEで追記した方が適切なコメントが付くかと。

Answer 2

sequelizeには詳しくないのですが、エラーメッセージは「productIdというカラム名が不正」ということを言っているように見えますね。

実際のテーブル定義にはproductIdというカラム名はなく、productテーブルのid列をproductIdとして扱いたいという意図はそれとなくコードから伝わってきましたが、実際のテーブル定義と実行したいSQL、所々で設定している（例えばforeign_key）productIdやproduct_idという名称が整合性が取れているのか再確認してみてはどうでしょうか。

Comments

[ss-nantoka]

ご回答いただきましてありがとうございます。
おそらく、order_detailテーブル結合部分のコードがエラー原因だとは思うのですが、countするのではなく単純な結合であれば、上手くいきましたので、productIdの整合性は問題ないかと思っています。