sequelize で、 結合させた2テーブルのうち 片一方のテーブルにしか存在しないデータ を取得したい

Question

タイトル通りとなりますが、結合させた状態で片一方にしか存在しないデータ取得を
LEFT JOIN と IS NULL でやろうと考えています。

環境:

"sequelize": "6.18.0",
"sequelize-cli": "6.4.1",
"sequelize-typescript": "2.1.3",
"mariadb": "3.0.0",
"typescript": "4.6.3",

studentsテーブルとteachersテーブルが多対多であり、２テーブルを中間テーブルa_students_teachersで紐づけています。

teachersと紐づかないstudentsを取得したいので、外部結合させてIS NULLでとってこれるものを取得したいのですが、うまくいっておらずアドバイス頂きたいです。

各テーブルのDDL

-- students
CREATE TABLE `students` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` tinytext NOT NULL COMMENT '学生名',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNQ:students:uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

-- teachers
CREATE TABLE `teachers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` tinytext NOT NULL COMMENT '教師名',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=824 DEFAULT CHARSET=utf8mb4

-- a_students_teachers
CREATE TABLE `a_students_teachers` (
  `student_id` bigint(20) unsigned NOT NULL COMMENT '学生ID',
  `teacher_id` bigint(20) unsigned NOT NULL COMMENT '教師ID',
  PRIMARY KEY (`student_id`,`teacher_id`),
  KEY `IDX:a_students_teachers:student_id` (`student_id`),
  KEY `IDX:a_students_teachers:teacher_id` (`teacher_id`),
  CONSTRAINT `FK:teachers:a_students_teachers` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK:students:a_students_teachers` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

SQLのイメージ（下記のSQLと同じことをSequelizeで行いたいです。）

（教師と紐付きがない、受け持ちが決まっていない生徒を出したいです）

select
  count(distinct students.id)
from
  students
  left join a_students_teachers
  on students.id = a_students_teachers.student_id
  inner join teachers
  on teachers.id = a_students_teachers.teachers_id
where
  a_students_teachers.teachers_id IS NULL

Repository（Sequelizeで実際に結果取得したいところはここに書いてます）

const count = await StudentModel.count({
  distinct: true,
  col: 'id',
  where: {corportionId: null} // ここではうまくいかなかった。id にしたりincludeの内側に書いてみたりしたがうまくいっていない
  include: [
    {
      required: false,
      model: teacherModel,
    }
  ]
});

return count;

いい書き方ご存知の方、ご教示くださいm(- -)m

Answer 1

GitHubのリポジトリをOp.colで検索したら{ '$User.id_user$': { [Op.col]: 'Tasks.user_id' } },のような記述を見つけました。
'$table_name.column_name$'で結合先の列を指定する方法がわかりました。
これと[Op.is]: nullを組み合わせると実現できそうでした。

https://github.com/sequelize/sequelize/blob/ac89aac7fa2b40a61517e2de661dd849cc3c2ca4/test/unit/sql/generateJoin.test.js
https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#operators

sqliteで試したコードも載せておきます。
countのSQLを発行する形にはなっていませんが、参考になりますでしょうか。

const { Sequelize, DataTypes, Model, Op } = require('sequelize');

const main = async () => {
    // Connecting to a database
    // Option 2: Passing parameters separately (sqlite)
    // https://sequelize.org/docs/v6/getting-started/#connecting-to-a-database
    const sequelize = new Sequelize({
        dialect: 'sqlite',
        storage: 'database.sqlite'
    });

    // Extending Model
    // https://sequelize.org/docs/v6/core-concepts/model-basics/#extending-model
    class Student extends Model { }
    Student.init({
        name: {
            type: DataTypes.TEXT,
        },
    }, {
        sequelize,
        modelName: 'students',
    })
    class Teacher extends Model { }
    Teacher.init({
        name: {
            type: DataTypes.TEXT,
        },
    }, {
        sequelize,
        modelName: 'teachers',
    })
    class StudentTeacher extends Model { }
    StudentTeacher.init({
    }, {
        sequelize,
        modelName: 'a_students_teachers',
    })
    Student.belongsToMany(Teacher, {
        through: StudentTeacher,
        foreignKey: 'student_id',
    })
    Teacher.belongsToMany(Student, {
        through: StudentTeacher,
        foreignKey: 'teacher_id',
    })

    // Model synchronization
    // https://sequelize.org/docs/v6/core-concepts/model-basics/#model-synchronization
    await Student.sync({ force: true })
    await Teacher.sync({ force: true })
    await StudentTeacher.sync({ force: true })

    // Simple INSERT queries
    // https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#simple-insert-queries
    const student1 = await Student.create({ name: '学生名1' })
    const student2 = await Student.create({ name: '学生名2' })
    const student3 = await Student.create({ name: '学生名3' })
    const student4 = await Student.create({ name: '学生名4' })
    const student5 = await Student.create({ name: '学生名5' })
    const teacher1 = await Teacher.create({ name: '教師名1' })
    const teacher2 = await Teacher.create({ name: '教師名2' })
    const teacher3 = await Teacher.create({ name: '教師名3' })
    const teacher4 = await Teacher.create({ name: '教師名4' })
    const teacher5 = await Teacher.create({ name: '教師名5' })

    // Special methods/mixins added to instances
    // Foo.belongsToMany(Bar, { through: Baz })
    // https://sequelize.org/docs/v6/core-concepts/assocs/#foobelongstomanybar--through-baz-
    await student1.addTeachers([teacher1, teacher2, teacher3])
    await student2.addTeachers([teacher2, teacher3, teacher4])
    await student4.addTeachers(teacher3)

    // Advanced M:N Associations
    // https://sequelize.org/docs/v6/advanced-association-concepts/advanced-many-to-many/#the-best-of-both-worlds-the-super-many-to-many-relationship
    const students = await Student.findAll({
        include: [{
            model: Teacher,
        }],
        where: {
            '$teachers.id$': {
                [Op.is]: null,
            },
        },
    })
    console.log(JSON.stringify(students))
    // [
    //     {
    //         "id": 3,
    //         "name": "学生名3",
    //         "createdAt": "2022-04-09T12:45:46.518Z",
    //         "updatedAt": "2022-04-09T12:45:46.518Z",
    //         "teachers": []
    //     },
    //     {
    //         "id": 5,
    //         "name": "学生名5",
    //         "createdAt": "2022-04-09T12:45:46.519Z",
    //         "updatedAt": "2022-04-09T12:45:46.519Z",
    //         "teachers": []
    //     }
    // ]
    console.log(students.length)
    // 2
}
main()

Comments

[aoisengaog]

ご回答ありがとうございました！
おかげさまで、やりたかった通りの条件で絞ることできました！

> '$table_name.column_name$'で結合先の列を指定する方法がわかりました。

こんなやり方があったのですね！
半ば諦めて、findAllにしてSequelize.literalなど使って取得することを検討していましたので、とても助かりました！
```
attributes: [
[Sequelize.fn("count", Sequelize.literal('`student->teacher`.`id` IS NULL OR NULL')), "count"],
],
```

お手数ですが、もし余裕があれば追加質問させてください...
今後は自力でこの今回教えていただいたやり方に辿り着けるようになりたいのですが、
今回どのように調べてこの書き方を見つけられましたでしょうか？
Githubで調べたときのことや、その他で調べたことやなにか簡単にで結構ですので、調べた簡単な手順やかかった時間など教えていただけませんでしょうか。

[退会済みユーザー]

コメントありがとうございます。

> 今回どのように調べてこの書き方を見つけられましたでしょうか？
> Githubで調べたときのことや、その他で調べたことやなにか簡単にで結構ですので、調べた簡単な手順やかかった時間など教えていただけませんでしょうか。

今回、初めてSequelizeを知って、試してみましたので、
質問内容の問題解決に関する時間以外に、使い方を学ぶ時間も必要だったのですが、
package.jsonを作り始めたのが、18:35のようでした。
それから途中に食事をしたりお風呂に入ったりはしましたが、
22:55に回答の文章が書き終わった感じでした。
なので、3~4時間くらいかかったでしょうか。
(回答をしてみて、私自身、勉強になります)

調べたことは、公式のチュートリアルから始めました。
簡単なinsertとselectができるところまで進めてみて、ようやく質問内容のスタートラインに立てました・・

最初、whereで結合先の列を指定する際に`teacher_id`とか`teacher.id`とか指定しても、
実際に発行されるSQLは`student.techer_id`とか`student.teacher.id`とかになってしまって、
Raw Queriesを使うしかないのかなとも思いました。

https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#operators
Model Querying - BasicsのOperatorsを見ていると、
`[Op.col]: 'user.organization_id'`のような記述があって、
これを使うと列の指定が正しくできるのではないかと思いました。
でも、これだけではどのような場合に使えるのかわかりませんでした。
(ドキュメントにはそれ以外に詳細な記述は見つけられませんでした・・)
(強引にwhereに記述してみましたが、エラーになりました・・)

`[Op.col]`の使い方を知りたかったので、GitHubのテストコードを探し始めました。
検索欄にOp.colと入力してリポジトリー内を検索しました。
すると、回答欄にも記載したテストコードが見つかりました。
https://github.com/sequelize/sequelize/blob/ac89aac7fa2b40a61517e2de661dd849cc3c2ca4/test/unit/sql/generateJoin.test.js

[Op.col]の使い方を知りたかったのですが、見つけたテストコードを見て、
今回の質問内容の問題を解決するためには使えなそうだということがわかりました。
でも、発行されるSQLの列を直接指定しているような記述(`'$User.id_user$'`)がたまたま隣にありました。
これが使えそうと思いましたので、実際に試してみました。
(その後、これ(`'$User.id_user$'`)に関する内容について、ちょっとドキュメントを探してみたのですが、見つけられませんでした。もしかしたら使ってはいけない記述だったりするのかもしれません・・)

こんな感じでした。
見つけられたのはたまたまでした・・
見つけられなかったらもっと時間もかかっていたかもしれません・・

[aoisengaog]

ご返答ありがとうございます！

時間かけて調べてくださったのですね...おかげさまで大変助かりました！
それでも3,4時間でたどり着けたのはびっくりしました...
下地がかなりあることが前提だとは思いますが、
同じ手順で辿り着けるように、わからないことはチュートリアルでも、最初から諦めず調べてみるようにします！

>`[Op.col]`の使い方を知りたかったので、GitHubのテストコードを探し始めました。

また、特にこちらが今回自分で調べた時ともっとも違うところだと感じました。
私の場合はまさに同じ状況になって、単に[Op.col]でぐぐるだけ、などをしてしまってたのですが、
公式のGithubのテストコードを調べるやり方は思いつかなかったので、積極的に使ってみるようにします

改めて、今回はどうもありがとうございました！