How to execute Laravel nested query-CodePudding

I have following 3 tables

Classrooms

id subject

RegisteredStudents

id user_id class_id

Posts

id class_id type attachment

Models

Classroom.php

public function posts()
{
   return $this->hasMany(Post::class);
}

RegisteredStudents.php

public function classroom()
{
  return $this->belongsTo(Classroom::class);
}

Post.php

public function classroom()
{
  return $this->belongsTo(Classroom::class);
}

Now I need to get all the posts of particular class, under which a student is registered.

for example, a student is registered for class which id no is 1 in classroom table. so it will be noted on registered_students table that this particular user is registered under this particular class. Every class may have multiple posts in post table. And the user need to get all post of his class.

I'm trying something like this:

 $registered_classes = RegisteredStudent::where('user_id',$user->id)
            ->where(function ($q) use ($class_id){
                Post::where('class_id',$class_id);
            })->get();

which is definitely not correct. what will be the correct query for this.

CodePudding user response：

You have created RegisteredStudent model, with classroom relation. The problem is, if your foreign_key is different with Laravel Behavior, you have to include it in the relation. Like this:

Classroom.php

public function posts(): HasMany
{
    return $this->hasMany(Post::class, 'class_id');
}

*Laravel Behavior need you to use classroom_id instead of class_id

RegisteredStudent.php

public function classroom(): BelongsTo
{
    return $this->belongsTo(Classroom::class, 'class_id');
}

public function user(): BelongsTo
{
    return $this->belongsTo(User::class);
}

Post.php

public function classroom(): BelongsTo
{
    return $this->belongsTo(Classroom::class, 'class_id');
}

More detailed about how Laravel Behavior with full examples here

*Edited

Then, if you want to get All Posts of a particular Class under which a student is registered, you could use with method:

public function getAllPostsClassroom()
{
    $registeredUser = RegisteredStudent::where('user_id', $user->id)->with(['classroom.posts' => function($query) use ($class_id) {
        $query->where('class_id', $class_id);
    }])->first();
}

And you will get All Posts inside the Classroom of a Registered User

CodePudding user response：

You first need to correct the relationships as TheArKa said in his answer (as the table and class names are not properly following the laravel eloquent convention).

Classroom.php

public function posts()
{
    return $this->hasMany(Post::class, 'class_id');
}

RegisteredStudent.php

public function classroom()
{
    return $this->belongsTo(Classroom::class, 'class_id');
}

Then the correct query should be:

$posts_of_class_of_user =
RegisteredStudent::where('user_id',$user->id)->classroom->posts;

This will first get the registered user from RegisteredStudent and then it will get the Classroom instance from belongsTo relatation and then it will get the all the related posts from hasMany relation.

Note that using relation, you can avoid complex queries specially in your case. You don't need any complex query. Just use the eloquent relational approach.