Laravel 9 relations with subquery-CodePudding

I'm working on an educational game, this is basically the scheme:

Important: I need a question to be in multiple languages. That is, a game will have many questions. And each question will have many languages.

Models: Game, GameQuestion, QuestionGroup, Question, Languague

Relationships:

Game:

public function game_questions()
{
    return $this->hasMany(GameQuestion::class);
}

GameQuestion:

public function question_groups()
{
   return $this->belongsToMany(QuestionGroup::class, 'questions','question_group_id');
}

QuestionGroup:

public function questions()
{
   return $this->hasMany(Question::class);
}

Question:

public function language()
{
  return $this->belongsTo(Language::class);
}

Language:

public function questions()
{
   return $this->hasMany(Question::class);
}

How can I retrieve with a search the collection of questions (many languages) added to the game?

$games = Game::where('user_id', $user_id)->orderBy('created_at', 'DESC')->with('game_question_question_group')->get();

I've already tried some consultations but I haven't had any luck. I appreciate the help.

CodePudding user response：

You need to use nested relationships:

$games = Game::where('user_id', $user_id)->with('game_questions.question_groups.questions')->orderBy('created_at', 'DESC')->get();

But honestly, the database could be designed better