テーブル結合時にSequelizeのfindOneメソッドが発行するSQL(リレーションシップ・Where句ごと)

初めに

SequelizeのfindOneメソッドを使用してテーブル結合を行った場合に、リレーションシップやWHERE句の有無などによって生成されるSQLが異なる。
どういった場合にどのようなSQLが生成されるか備忘録として投稿する。

基本的なテーブル設計

• Parents（親テーブル）
  • id （PRIMARY KEY）
• Children
  • id（PRIMARY KEY）
  • parent_id（FOREIGN KEY）

※テーブルのカラムは臨機応変に変更するが、基本的には上記のテーブル設計で行う。

環境

■ MySQLバージョン
5.7.35
■ Sequelizeバージョン
3.30.4

1対多

Sequelizeのリレーションシップ

Parents.hasMany(models.Children, {
    foreignKey: 'parent_id'
});
Children.belongsTo(models.Parents, {
    foreignKey: 'parent_id'
});

親テーブルに子テーブルを結合

ORM

Parents.findOne({
    include: [Children]
})

SQL

SELECT
    `Parents`.*,
    `Children`.`id` AS `Children.id`,
    `Children`.`parent_id` AS `Children.parent_id`,
FROM
    (
        SELECT
            `Parents`.`id`,
        FROM
            `Parents` AS `Parents`
        LIMIT
            1
    ) AS `Parents`
    LEFT OUTER JOIN `Children` AS `Children` ON `Parents`.`id` = `Children`.`parent_id`;

findOneは親テーブルの1件のレコードを対象とするので、サブクエリで1件のレコードを取得してからテーブル結合を行っている。
また、親テーブルに子テーブルが複数存在している可能性があるため内部結合でなく外部結合されており、LIMIT句もメインクエリでは発行されていない。

親テーブルに子テーブルを結合して子テーブルにWHERE句を付与

ORM

子テーブルのPRIMARY KEYをWHERE句で指定する。

Parents.findOne({
    include: [
        {model: sequelize.models.Children, where: {id: 1}}
    ]
})

SQL

SELECT
    `Parents`.*,
    `Children`.`id` AS `Children.id`,
    `Children`.`parent_id` AS `Children.parent_id`,
FROM
    (
        -- 2番
        SELECT
            `Parents`.`id`,
        FROM
            `Parents` AS `Parents`
        WHERE
            (
                -- 1番
                SELECT
                    `parent_id`
                FROM
                    `Children` AS `Children`
                WHERE
                    (
                        `Children`.`parent_id` = `Parents`.`id`
                        AND `Children`.`id` = 1
                    )
                LIMIT
                    1
            ) IS NOT NULL
        LIMIT
            1
    ) AS `Parents`
    INNER JOIN `Children` AS `Children` ON `Parents`.`id` = `Children`.`parent_id`
    AND `Children`.`id` = 1;

テーブルのリレーションシップは1対多であるが、子テーブルのPRIMARY KEYを指定することで取得するレコードは両テーブルとも1件ずつになる。
よって2番とコメントしたサブクエリで、WHERE句で指定した子テーブルが存在することを担保し、LIMIT句で1件のレコードを取得後に子テーブルを内部結合させている。
メインクエリでLIMIT句を使用していないのは、PRIMARY KEYをWHERE句で指定して取得できるレコードが1件のみと担保できている子テーブルを内部結合しているためであると考えられる。

以下はSQLの詳細に関して記載している。

1番とコメントしたサブクエリのWHERE句内のサブクエリで、子テーブルに指定したWHERE句に当てはまるレコードを1件取得する。
2番とコメントしたサブクエリで、1番で取得したレコードが存在していれば親テーブルのレコードを1件取得する。
2番で取得した親テーブルのレコード1件に対して子テーブルを内部結合させる。

親テーブルに子テーブルを結合して子テーブルにwhere句と外部結合を付与

ORM

子テーブルのPRIMARY KEYをWHERE句で指定する。

Parents.findOne({
    include: [
        {model: sequelize.models.Children, where: {id: 1}, required: false}
    ]
})

SQL

SELECT
    `Parents`.*,
    `Parents`.`id` AS `Parents.id`,
    `Parents`.`parent_id` AS `Parents.parent_id`,
FROM
    (
        SELECT
            `Parents`.`id`,
        FROM
            `Parents` AS `Parents`
        LIMIT
            1
    ) AS `Parents`
    LEFT OUTER JOIN `Children` AS `Parents` ON `Parents`.`id` = `Parents`.`parent_id`
    AND `Parents`.`id` = 1;

WHERE句で子テーブルのPRIMARY KEYを指定することでよりも、required: falseの記述の方が優先されて外部結合されるようだ。

親テーブルに子テーブルを結合（PRIMARY KEYの指定なし/whereあり）

ORM

no_primatyというPRIMARY KEYではないカラムを指定する。

Parents.findOne({
    where: {no_primaty: 1},
    include: [Children]
})

SQL

SELECT
    `Parents`.*,
    `Children`.`id` AS `Children.id`,
    `Children`.`parent_id` AS `Children.parent_id`,
FROM
    (
        SELECT
            `Parents`.`id`,
            `Parents`.`no_primaty`,
        FROM
            `Parents` AS `Parents`
        WHERE
            `Parents`.`no_primaty` = '1'
        LIMIT
            1
    ) AS `Parents`
    LEFT OUTER JOIN `Children` AS `Children` ON `Parents`.`id` = `Children`.`parent_id`;

親テーブルのPRIMARY KEYでないカラムをWHERE句で指定してもサブクエリは生成される。
findOneメソッドは親テーブルを１件取得するため、PRIMARY KEYではないカラムをwher句を使用して指定しても親テーブルのレコードを複数取得する可能性があるため、サブクエリ内でLIMIT句を使用して親テーブルを1件のみ取得するSQLを発行しているようだ。

親テーブルに子テーブルを結合（親テーブルのPRIMARY KEYを指定）

ORM

Parents.findOne({
    where: {id: 1},
    include: [Children]
})

SQL

SELECT
    `Parents`.`id`,
    `Children`.`id` AS `Children.id`,
    `Children`.`parent_id` AS `Children.parent_id`,
FROM
    `Parents` AS `Parents`
    LEFT OUTER JOIN `Children` AS `Children` ON `Parents`.`id` = `Children`.`parent_id`
WHERE
    `Parents`.`id` = 1;

親テーブルのPRIMARY KEYを指定するとFORM句内のサブクエリは生成されない。
「PRIMARY KEYを指定 = 親テーブルの取得するレコードは1件のみ」の条件がそろうため、サブクエリを発行して親テーブルの取得件数を1件に絞る必要がなくサブクエリが発行されないと考えられる。

子テーブルに親テーブルを結合

ORM

Children.findOne({
    include: [Parents]
})

SQL

SELECT
    `Children`.`id`,
    `Children`.`parent_id`,
    `Parents`.`id` AS `Parents.id`,
FROM
    `Children` AS `Children`
    LEFT OUTER JOIN `Parentss` AS `Parents` ON `Children`.`parent_id` = `Parents`.`id`
LIMIT
    1;

サブクエリは発行されずに親テーブルが外部結合される。

1対1

Sequelizeのリレーションシップ

Parents.hasOne(models.Children, {
    foreignKey: 'parent_id'
});
Children.belongsTo(models.Parents, {
    foreignKey: 'parent_id'
});

親テーブルに子テーブルを結合

ORM

Parents.findOne({
    include: [Children]
})

SQL

SELECT
    `Parents`.`id`,
    `Children`.`id` AS `Children.id`,
    `Children`.`admin_id` AS `Children.admin_id`,
FROM
    `Parents` AS `Parents`
    LEFT OUTER JOIN `Children` AS `Children` ON `Parents`.`id` = `Children`.`admin_id`
LIMIT
    1;

1対多のリレーションシップだとサブクエリが発行されて、親テーブルのレコードを1件のみに絞っていたが、1対1だとサブクエリは発行されずに子テーブルが外部結合される。

子テーブルに親テーブルを結合

ORM

Children.findOne({
    include: [Parents]
})

SQL

SELECT
    `Children`.`id`,
    `Children`.`parent_id`,
    `Parents`.`id` AS `Parents.id`,
FROM
    `Children` AS `Children`
    LEFT OUTER JOIN `Parentss` AS `Parents` ON `Children`.`parent_id` = `Parents`.`id`
LIMIT
    1;

こちらもサブクエリは発行されずに親テーブルが外部結合される。

まとめ

Sequelizeが細かい条件や設定によって発行するSQLを変更していることが分かった。
全て暗記する必要はないが、条件によってSQLが変更されており取得できる値も変わっていることを認識する必要があるように感じた。