サブクエリ

Post と Reaction という 2 つのモデルがあり、1 対多の関係が設定されているため、1 つの投稿に多くのリアクションがあるとします。

const Post = sequelize.define(
  'post',
  {
    content: DataTypes.STRING,
  },
  { timestamps: false },
);

const Reaction = sequelize.define(
  'reaction',
  {
    type: DataTypes.STRING,
  },
  { timestamps: false },
);

Post.hasMany(Reaction);
Reaction.belongsTo(Post);

注: 次の例ではクエリを短くするためにタイムスタンプを無効にしています。

テーブルにデータをいくつか入力してみましょう。

async function makePostWithReactions(content, reactionTypes) {
  const post = await Post.create({ content });
  await Reaction.bulkCreate(reactionTypes.map(type => ({ type, postId: post.id })));
  return post;
}

await makePostWithReactions('Hello World', [
  'Like',
  'Angry',
  'Laugh',
  'Like',
  'Like',
  'Angry',
  'Sad',
  'Like',
]);
await makePostWithReactions('My Second Post', ['Laugh', 'Laugh', 'Like', 'Laugh']);

これで、サブクエリの威力を示す例の準備ができました。

各投稿の laughReactionsCount を SQL 経由で計算したいとしましょう。これは、次のようなサブクエリを使用して実現できます。

SELECT
    *,
    (
        SELECT COUNT(*)
        FROM reactions AS reaction
        WHERE
            reaction.postId = post.id
            AND
            reaction.type = "Laugh"
    ) AS laughReactionsCount
FROM posts AS post

上記の生の SQL クエリを Sequelize で実行すると、次のようになります。

[
  {
    "id": 1,
    "content": "Hello World",
    "laughReactionsCount": 1
  },
  {
    "id": 2,
    "content": "My Second Post",
    "laughReactionsCount": 3
  }
]

では、生のクエリ全体を手書きすることなく、Sequelize の助けを借りてこれをどのように実現できるでしょうか？

答え: ファインダーメソッド (findAll など) の attributes オプションと、任意のコンテンツをエスケープせずにクエリに直接挿入できる sequelize.literal ユーティリティ関数を組み合わせることによってです。

これは、Sequelize はメインの大きなクエリの作成を支援しますが、サブクエリは自分で記述する必要があることを意味します。

Post.findAll({
  attributes: {
    include: [
      [
        // Note the wrapping parentheses in the call below!
        sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM reactions AS reaction
                    WHERE
                        reaction.postId = post.id
                        AND
                        reaction.type = "Laugh"
                )`),
        'laughReactionsCount',
      ],
    ],
  },
});

重要な注意: sequelize.literal はエスケープせずに任意のコンテンツをクエリに挿入するため、(重大な) セキュリティ脆弱性の原因となる可能性があるため、特別な注意が必要です。ユーザー生成コンテンツには使用しないでください。ただし、ここでは、sequelize.literal を、私たち (コーダー) によって慎重に記述された固定文字列で使用しています。これは、私たちが何をしているかを知っているため、問題ありません。

上記は次の出力を生成します。

[
  {
    "id": 1,
    "content": "Hello World",
    "laughReactionsCount": 1
  },
  {
    "id": 2,
    "content": "My Second Post",
    "laughReactionsCount": 3
  }
]

成功!

複雑な順序付けのためのサブクエリの使用

この考え方は、投稿を笑いのリアクションの数で並べ替えるなど、複雑な順序付けを可能にするために使用できます。

Post.findAll({
  attributes: {
    include: [
      [
        sequelize.literal(`(
                    SELECT COUNT(*)
                    FROM reactions AS reaction
                    WHERE
                        reaction.postId = post.id
                        AND
                        reaction.type = "Laugh"
                )`),
        'laughReactionsCount',
      ],
    ],
  },
  order: [[sequelize.literal('laughReactionsCount'), 'DESC']],
});

結果

[
  {
    "id": 2,
    "content": "My Second Post",
    "laughReactionsCount": 3
  },
  {
    "id": 1,
    "content": "Hello World",
    "laughReactionsCount": 1
  }
]