mysql - 带计数的子选择

Question

我有一个 sql 查询，我想用 sequelize 代码编写它。它是一个选择，嵌套有一个选择计数。谁能帮我存档吗？

我希望这个原始查询能够与 nodejs 中的 sequelize 一起使用，但我不想使用原始查询。

最后应该是这样的:

{product: [included product model], stock count: 5}

SELECT 
  products.id as 'ID', 
  (SELECT COUNT(*) 
   from stocks 
   WHERE stocks.fk_product = products.id 
     AND stocks.sold = 0) AS 'counts' 
FROM products

我真的不知道该怎么做...

Answer 1

SELECT COUNT(*) as `count`, s.fk_product as `ID` from stocks as s WHERE s.sold = 0 group by s.fk_product