Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

carcleo's avatar

Removing relations that not satisfade

i am trying change the this eloquent builder

$students = DiaryFrequency::select('students.name as student_name',
                        'students.id as student_id', 
                        'diaries.id as diary_id',
                        'diary_frequency.presence as presence', 
                        'classrooms.id as classroom_id',
                        'classrooms.code as classroom_code'
                    )
                    ->join("students", "students.id", "=", "diary_frequency.student_id")
                    ->join("diaries", "diaries.id", "=", "diary_frequency.diary_id")
                    ->join("classrooms", "diaries.classroom_id", "=", "classrooms.id")
                    ->where([
                        "diaries.classroom_id"=> $fields["classroom_id"],
                        "diaries.date"=> $fields["date"],
                    ])
                    ->distinct("distinct students.name")
                    ->orderBy("students.name")
                    ->get();

to this eloquent query

$students = Student::select('id','name')
                    ->whereHas("diaries", function($query) use ($fields){
                        $query->where([
                            "classroom_id"=> $fields["classroom_id"],
                            "date"=> $fields["date"]
                        ]);
                    })
                    ->with("diaryFrequency", function($query) {
                        $query->select('student_id','presence');
                    })
                    ->orderBy("name")
                    ->get();

and i have a problem with it: The 'presence's' is outing to all classrooms and i need that it out only to $fields["classroom_id"].

The relation:

class Student extends Model {    
....
public function diaryFrequency() : HasMany {
    return $this->hasMany(DiaryFrequency::class);
}

The Model DiaryFrequency has a relation with diary:

class DiaryFrequency extends Model {
  ...
  public function diaries () : BelongsTo {
      return $this->belongsTo(Diary::class, 'diary_id');
  }

What i do to exlude the relations that dont be of $fields["classroom_id"]?

0 likes
9 replies
MichalOravec's avatar

You have to filter the with part as well

$students = Student::select('id', 'name')
    ->whereHas("diaries", function ($query) use ($fields) {
        $query->where([
            'classroom_id' => $fields['classroom_id'],
            'date' => $fields['date']
        ]);
    })
    ->with('diaryFrequency', function ($query) use ($fields) {
        $query->select('student_id', 'presence')
            ->whereRelation('diaries', 'classroom_id', $fields['classroom_id']);
    })
    ->orderBy('name')
    ->get();

https://laravel.com/docs/11.x/eloquent-relationships#inline-relationship-existence-queries

carcleo's avatar

@MichalOravec continue listing the not wanted

"diary_frequency" => array:3 [
      0 => array:2 [
        "student_id" => 5
        "presence" => 0
      ]
      1 => array:2 [
        "student_id" => 5
        "presence" => 0
      ]
      2 => array:2 [
        "student_id" => 5
        "presence" => 1
      ]
    ]
  ]
MichalOravec's avatar

@carcleo Add diary_id to the select statement:

->with('diaryFrequency', function ($query) use ($fields) {
        $query->select('student_id', 'presence', 'diary_id')
            ->whereRelation('diaries', 'classroom_id', $fields['classroom_id']);
    })
carcleo's avatar

Lets go:

I need the follow:

i have 4 tables: students, diaries, diary_frequency, classrooms

diaries have => id, classroom_id, discipline_id, date

diary_frequency have => id, diary_id, student_id, presence

i need return all students that was in classroom_id in discipline_id in ths date

i have the 2 select box, a to list the disciplines, e depending of the discipline choosed it reurn another select of classrooms that, depending of classroom choosed, it return the students in he date

Thunderson's avatar

your explanations are not clear if you see that we cannot answer it is because we have difficulty understanding. tell us the query result you want to get, the table involved and their relationships.

carcleo's avatar

@Thunderson i realize that need before get the diary.id to compare.

But, in this moment, i have more than one resuts to diary because i whas not filter the previous select, then i need to do it

$classrooms = 
   Classroom::select("id",'code')
                   ->whereHas("disciplines_diaries", function($dd) use ($fields){
                                $dd->where("date", $fields["date"]);
                                 $dd->where("discipline_id", $fields["discipline_id"]);
                    })
                    ->with("disciplines_diaries", function($dd) use ($fields, $this){
                          $dd->select("diaries.id")
                                 ->where([
                                       "discipline_id"=> $fields["discipline_id"],
                                       "classroom_id"=> $this.id,
                                 ]);
                     })
                     ->orderBy("code")
                     ->get();

But i need in this query get at

 ->with("disciplines_diaries", function($dd) use ($fields, $this){

the $this as Classroom::select("id") for each` classroom_id togheter with discipline_id and realize whal is the diary_id associed

Thunderson's avatar

@carcleo What is the kind of relationships between classroom and disciplines_diaries. i think classroom hasMany disciplines_diaries so when you eager load disciplines_diaries all disciplines_diaries loaded are already linked to the classroom_id. why do you need to specify classroom_id again. check this

Classroom::with('disciplines_diaries')->find(...)

You will see classrooms.id and disciplines_diaries.classroom_id are always the same.

carcleo's avatar

@Thunderson Thank, i got it

$students = Student::select('id','name')
                    ->whereHas("diaries", function($query) use ($fields){
                        $query->where([
                            "classroom_id"=> $fields["classroom_id"],
                            "discipline_id"=> $fields["discipline_id"],
                            "date"=> $fields["date"]
                        ]);
                    })
                    ->with("diaryFrequency", function($query) {
                        $query->select('student_id','presence');
                    })
                    ->orderBy("name")
                    ->get();

just insert one more field "discipline_id"=> $fields["discipline_id"], received of the front by JavaScript $.ajax() and it soved of problem

1 like

Please or to participate in this conversation.