Laravel / SQL 3つのテーブルを結合させたい

Question

前提・実現したいこと

Model内で3つのテーブルを結合させ、コントローラーに渡したいです。
可能であれば、countEvaluationsメソッド及びgetTeachersPointQueryメソッドは残した状態で、解決したいです。

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

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'evaluations' (SQL: select `users`.*, `evaluations`.`reviews` as `reviews`, `evaluations`.`avg_point` as `evaluations_avg_point` from `users` left join (select `evaluations`.`user_teacher_id`, count('user_teacher_id') as reviews from `evaluations` where `evaluations`.`deleted_at` is null group by `evaluations`.`user_teacher_id`) as `evaluations` on `users`.`id` = `evaluations`.`user_teacher_id` left join (select `evaluations`.`user_teacher_id`, avg(evaluations.point) as avg_point from `evaluations` where `evaluations`.`deleted_at` is null group by `evaluations`.`user_teacher_id`) as `evaluations` on `users`.`id` = `evaluations`.`user_teacher_id` where `users`.`id` = 2 and `users`.`deleted_at` is null limit 1)

該当のソースコード

public function countEvaluations()
{
    // 講師別のレビュー数を取得
    return Evaluation::query()
        ->select(
            'evaluations.user_teacher_id', DB::raw("count('user_teacher_id') as reviews")
        )
        ->groupBy('evaluations.user_teacher_id');
}

public function getTeachersPointQuery()
{
    // 講師別の評価値を集計
    return Evaluation::query()
        ->select(
            'evaluations.user_teacher_id',
            DB::raw('avg(evaluations.point) as avg_point')
        )
        ->groupBy('evaluations.user_teacher_id');
}

public function show($id)
{
    // 講師のレビュー数を取得
    $reviews     = $this->countEvaluations();
    // 講師の評価を取得
    $evaluations = $this->getTeachersPointQuery();
    return self::query()
        // 講師のレビュー数
        ->leftJoinSub($reviews, 'evaluations', function ($join) {
            $join->on('users.id', '=', 'evaluations.user_teacher_id');
        })
        // 講師の評価
        ->leftJoinSub($evaluations, 'evaluations', function ($join) {
            $join->on('users.id', '=', 'evaluations.user_teacher_id');
        })
        ->where('users.id', '=', $id)
        ->select([
            'users.*',
            'evaluations.reviews as reviews',
            'evaluations.avg_point as evaluations_avg_point',
        ]);
}

テーブル構造

evaluations
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | bigint unsigned  | NO   | PRI | NULL    | auto_increment |
| user_student_id | int unsigned     | NO   |     | NULL    |                |
| user_teacher_id | int unsigned     | NO   |     | NULL    |                |
| lesson_id       | int unsigned     | NO   |     | NULL    |                |
| point           | tinyint unsigned | NO   |     | NULL    |                |
| comment         | varchar(255)     | NO   |     | NULL    |                |
| created_at      | timestamp        | YES  |     | NULL    |                |
| updated_at      | timestamp        | YES  |     | NULL    |                |
| deleted_at      | timestamp        | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+

users
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | bigint unsigned  | NO   | PRI | NULL    | auto_increment |
| name              | varchar(255)     | NO   |     | NULL    |                |
| email             | varchar(255)     | NO   | UNI | NULL    |                |
| email_verified_at | timestamp        | YES  |     | NULL    |                |
| password          | varchar(255)     | NO   |     | NULL    |                |
| account           | varchar(255)     | NO   | UNI | NULL    |                |
| status            | tinyint unsigned | YES  |     | NULL    |                |
| sex               | tinyint unsigned | NO   |     | 0       |                |
| age               | tinyint unsigned | YES  |     | NULL    |                |
| country_id        | tinyint unsigned | YES  |     | NULL    |                |
| prefecture_id     | int unsigned     | YES  |     | NULL    |                |
| language_id       | int unsigned     | YES  |     | NULL    |                |
| img               | varchar(255)     | YES  |     | NULL    |                |
| profile           | varchar(1000)    | NO   |     | NULL    |                |
| mailing           | tinyint unsigned | NO   |     | NULL    |                |
| bank_type         | tinyint unsigned | YES  |     | NULL    |                |
| bank_id           | int unsigned     | YES  |     | NULL    |                |
| credit_id         | int unsigned     | YES  |     | NULL    |                |
| category1_id      | tinyint unsigned | YES  |     | NULL    |                |
| category2_id      | tinyint unsigned | YES  |     | NULL    |                |
| category3_id      | tinyint unsigned | YES  |     | NULL    |                |
| category4_id      | tinyint unsigned | YES  |     | NULL    |                |
| category5_id      | tinyint unsigned | YES  |     | NULL    |                |
| withdraw_reason   | varchar(255)     | YES  |     | NULL    |                |
| remember_token    | varchar(100)     | YES  |     | NULL    |                |
| created_at        | timestamp        | YES  |     | NULL    |                |
| updated_at        | timestamp        | YES  |     | NULL    |                |
| deleted_at        | timestamp        | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

試したこと

leftJoinにしたり、joinにしたり、試しましたが、うまくいきませんでした。
また、countEvaluations及びgetTeachersPointQueryを、1つのみ実行した場合は、問題なく実行されます。

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

Laravel Framework 8.14.0
PHP 7.4.9

Comments

[phper.k]

さすがにテーブルの構造や意味がわからないと回答できませんがな…

[nanami0221]

申し訳ございません、追記いたします、、、

[phper.k]

Modelのリレーションの定義はどうなっていますか？

[nanami0221]

申し訳ございません、hasmanyなどの定義が、Model内にあるか、という意味でしょうか。
であれば、何も定義しておりません。

[phper.k]

あ、そういうことですか。
Join とかしているから変だなと思ったけど、Laravel 使っているなら、Relation を使うのが普通だし、そのほうが楽ですよー

[nanami0221]

hasmanyなどですよね？
一度やってみたのですが、うまくいかなかったので、諦めてslq文で書いてしまいました、、、

有難うございます！
Relationでの実装も、試してみます！

[phper.k]

そうですね、HasMany, BelongsTo, BelongsToMany の３つでほとんどのシステムカバーできます。
それ以外はほぼおまけ

[m.ts10806]

いきなりLaravelからやるより、まずは「想定の結果を得られるSQL」を書いてからの方が実装イメージがわくのでは。

[nanami0221]

有難うございます！
Sequel Proという便利なツールを見つけましたので、SQLで色々試してみます！

Answer 1

質問への解決はしていないですが、いきなりLaravelではなく、SQLの基本的な箇所から勉強していきたいと思います。
私も初めて知ったのですが、Sequel Proというツールを用いれば、SQL文を色々試せるので、とても勉強になります。

Comments

[nanami0221]

追記で、やりたいことが、SQL文では完成しました！

SELECT users.*,
COUNT(user_teacher_id) AS reviews_count,
AVG(point) AS avg_point
FROM users LEFT JOIN evaluations
ON users.id=evaluations.user_teacher_id
GROUP BY users.id;