CODEWITHSUNDEEP
mysqlsqllaraveleloquentparent-child
Raymond
Raymond

Reputation: 5

Get data based on parent table's another child

I have 3 tables. The relationships are as below: ERD of my tables

Student

Parent

Exam

I want to get the parents where country is "Malaysia", and the students that having subject = "English" and grade = "A". How can I write the SQL? I am using Laravel for my project, how to make this in the Eloquent?

Upvotes: 0

Views: 500

Answers (1)

Cory Sheppard
Cory Sheppard

Reputation: 31

If I'm understanding this correctly, you essentially want all the students who have parents from Malaysia and have a grade of A in English. To get them, I would do something along the lines of:

Student::whereHas('parent', function ($query) {
    $query->where('country', 'Malaysia')
})->whereHas('exams', function ($query) {
    $query->where('subject', 'English')->where('grade', 'A')
})->get()

The whereHas method (https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence) uses the relationship to a model to query it. Therefore this is done with the assumption your relationships on the Student model are like so:

public function parent()
{
    return $this->hasOne(Parent::class);
}

public function exams()
{
    return $this->hasMany(Exam::class);
}

Include how you've written your relationships on your models in the question next time, it provides a lot more context than an ERD.

Upvotes: 1

Related Questions