CODEWITHSUNDEEP
phpsql-serverlaravel-5eloquent
ashanrupasinghe
ashanrupasinghe

Reputation: 756

Laravel - whereHas checking latest record of a relationship without checking others

I have two tables that has One to Many relationship.

Bookings - (id)

booking_tasks - (id, booking_id,user_id)

one booking has many task one task has one booking

Booking Model:

 public function tasks() {
    return $this->hasMany('App\BookingTask');    
  }

BookingTask Model

public function booking() {
    return $this->belongsTo('App\Booking');
}

I want to get list of bookings that, user_id = 2 for latest booking_task for the bookings. I do not want to check other old booking_tasks of the bookings.

ex:

enter image description here

I want to check whether the last record of the booking_task's user_id=2 then get it as a booking. In the example last booking_task's user_id = 5. So it will not get as booking.

My code is:

$bookings=Booking::whereHas('tasks',function($q){
            $q->where('user_id',2);//this will check whether any of record has user_id =2
})->get();

I used this also: But it is not a correct one,

$bookings=Booking::whereHas('tasks',function($q){
                $q->latest()->where('user_id',2)->limit(1);//this will check whether any of record has user_id =2 and return latest one.
    })->get();

Ho can I solve this problem, I have to use Laravel Eloquent also.

Upvotes: 2

Views: 6335

Answers (4)

Simon
Simon

Reputation: 1822

I found the answer from Jonas worked but because it was a query with a join, it caused issues with eager loading relations from the model. So I used that solution with the whereHas function to come up with a solution that could be used in scopes and so would be reusable.

The first step involves adding a macro to the query Builder in the AppServiceProvider.

use Illuminate\Database\Query\Builder;

Builder::macro('whereLatestRelation', function ($table, $parentRelatedColumn) 
{
    return $this->where($table . '.id', function ($sub) use ($table, $parentRelatedColumn) {
        $sub->select('id')
            ->from($table . ' AS other')
            ->whereColumn('other.' . $parentRelatedColumn, $table . '.' . $parentRelatedColumn)
            ->latest()
            ->take(1);
    });
});

This basically makes the sub-query part of Jonas's answer more generic, allowing you to specify the join table and the column they join on. It assumes the other column is an 'id' column, so it can be improved further. To use this you'd then be able to do:

$userId = 2;
Booking::whereHas('tasks', function ($tasks) use ($userId) {
    $tasks->where('user_id', $userId)
        ->whereLatestRelation((new Task)->getTable(), 'booking_id');
});

It's the same logic as the accepted answer, but a bit easier to re-use. It will, however, be a little slower, but that should be worth the re-usability.

Upvotes: 2

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

This requires a more complex query:

$bookings = Booking::select('bookings.*')
    ->join('booking_tasks', 'bookings.id', 'booking_tasks.booking_id')
    ->where('booking_tasks.user_id', 2)
    ->where('booking_tasks.id', function($query) {
        $query->select('id')
            ->from('booking_tasks')
            ->whereColumn('booking_id', 'bookings.id')
            ->latest()
            ->limit(1);
    })->get();

Upvotes: 14

John Halsey
John Halsey

Reputation: 2008

This

I want to get list of bookings that, user_id = 2 for latest booking_task for the bookings. I do not want to check other old booking_tasks of the bookings.

doesn't make an awful lot of sense, but what I think you want is to get out is all the bookings with user_id == 2, where has at least 1 task and with only the lastest task for each booking? So perhaps this will work?

Booking::where('user_id', 2)->with('tasks', function($query){
    $query->orderBy('created_at', 'DESC')->take(1);
})->has('tasks')->get();

Upvotes: 0

Sergey Poprovka
Sergey Poprovka

Reputation: 11

Have you tried ->first() combined with reversed orderBy()?

Like $q->where(‘user_id’,’=‘,’2’)->orderBy(‘id’,’DESC’)->first();

Upvotes: -1

Related Questions