How to translate the SQL query to Laravel Eloquent query?-CodePudding

I'm trying to make a complex query using Laravel Eloquent. I know how to do it using raw SQL query, but I don't have any idea how to do it using Eloquent. Here is my SQL query, and it works perfectly:

select *
from students
where exists(select *
             from (select student_movements.id AS sm_id, student_movements.direction, student_movements.deleted_at
                   from student_movements
                            inner join student_student_movements
                                       on student_movements.id = student_student_movements.student_movement_id
                   where students.id = student_student_movements.student_id
                     and student_movements.deleted_at is null
                   order by student_movements.id desc
                   limit 1) as last_sm
             where last_sm.direction = 1 AND last_sm.date >= 5-5-2022
          );

My models have many-to-many relation using student_student_movements table:

Student

    public function studentMovements(): BelongsToMany
    {
        return $this->belongsToMany(
            StudentMovement::class,
            'student_student_movements',
        );
    }

StudentMovement

    public function students(): BelongsToMany
    {
        return $this->belongsToMany(
            Student::class,
            'student_student_movements'
        );
    }

My goal is to get all Students, who have the last Movement where direction = 1 and the date of the last Movement >= $someDate. So, my question is: how to translate the SQL query to Eloquent? I saw many similar questions, but they are not helping me.
Thanks for any advice.

CodePudding user response：

Use the whereHas method, then fine tune the sub query inside the closure to your needs.

CodePudding user response：

You can use the whereHas and orWhereHas methods to define additional query constraints on your has queries.

There is an example like that in laravel documentation

use Illuminate\Database\Eloquent\Builder;
 
// Retrieve posts with at least one comment containing words like code%...
$posts = Post::whereHas('comments', function (Builder $query) {
    $query->where('content', 'like', 'code%');
})->get();
 
// Retrieve posts with at least ten comments containing words like code%...
$posts = Post::whereHas('comments', function (Builder $query) {
    $query->where('content', 'like', 'code%');
}, '>=', 10)->get();

check the documentation here