whereHas latest child equals a value

Hi @patrykszady,

You could try something like this:

$projects = Project::whereHas('projectstatuses', function ($query) {
    $query->latest()->where('status_id', 5);
})->get();

Then, if you would like to use it as a scope:

public function scopeActive(){
    return $query->whereHas('projectstatuses', function ($q) {
         $q->latest()->where('status_id', 5);
    });
}

Hope it helps.

Kind regards, Darek

@Bandab Thanks Darek --- I thought I tried every $query->where/latest last night. This works great and is very clean. Exactly what I was hoping for.

@Bandab Actually, I jumped the gun because that still gives me all Projects as long as they have status_id of 5, it doesn't matter if status_id 6 or 7 or 8 was created after the status_id 5.

It shows as active, even if the project is in another state.

So I can get what I'm looking for with this

public function latestStatus(){
        return $this->hasOne('App\Projectstatus')->latest()->limit(1)->first()->title_id;
    } 

This returns 4,5,6, etc. and then I can easily use this, however how would I make this work in a scope?

Wow, this was difficult for me.

On my Project Model

    public function scopeActive($query){
        return $query->with('latestStatus')->get()->where('latestStatus.title_id', 5);
    }

    public function latestStatus(){
        return $this->hasOne('App\Projectstatus')->latest();
    }

@PATRYKSZADY - This should be considered best answer. This is the best answer I could find thus far that touches this topic, even though it uses the where function of eloquent.

It doesnt work! When you use HasOne(...)->latest() you only are adding an orderBy() sql but it still will consider all related records, so, resulted sql will not be limited to ONLY the last record... If you use a '<=' comparison, this query will still give you all records instead of searching for ONLY the last one... Some one know how to face it with query builder? I think its not possible and you have to use DB::raw() thing.

I'm having the exact same issue right now, will let you know if I come across anything. It seems like the problem is not being able to limit 1 on orderBy()

I seriously cannot believe that there isn't a way to do this with the query builder without using raw sql! What's the point of all those abstractions if they cannot generate a relatively simple query.

BTW, if your Project model, you can do

public function status(){
    return $this->hasOne(ProjectStatus::class)->latest('id');
}

and $project->status->status_id will give you the latest status for that project instance.

Now, back to SQL. I've tested the following raw SQL and it works

SELECT * 
FROM projects AS p
LEFT JOIN project_statuses AS ps
ON ps.id = ( SELECT ps1.id FROM project_statuses AS ps1 WHERE p.id = p1.project_id ORDER BY p1.id DESC LIMIT 1 )
WHERE ps.status = 5;

I see that there's joinSub in Laravel now but I couldn't get it to generate anything close to this. No joins I see can do the subquery in the on clause.

My solution in an identical situation now is to add a status column to the project table. I use a ProjectRepository and ProjectStatusRepository to handle all Model interactions with the application there, i.e. I'll be just updating the projects.status column as well as creating a new project_statuses entry

Just wanted to thank you @kondorb for your answer. Even years after, this thread has been very useful for me since I couldn't find an answer anywhere else.

Perhaps it would be helpful if @patrykszady could select your answer as best answer, to save time to other people looking for a working solution to this problem.

@kondorb Thank you very much! I needed to compare data for first relationship and couldn't figure out how to do this in Laravel. Again, thank you very much!

I maybe a noob here, but after days of shoveling I am unable to find a solution so posting here.

I am in a similar situation.

I have SaleInvoice model and Payment model under a Ploymorphic relationship of One SaleInvoice HasMany Payments & Each Payment BelongsTo A SaleInvoice.

As multiple payments are made, the last polymorph model of Payment is marked as closed and till then it is as due in field, payment_status in the Payment model itself.

Now, I need to fetch the SaleInvoices where the latest Payment's payment_status is not closed. (I am saying 'not closed ' and not 'as due' because there are other payment_status like overdue)

From the answer of @kondorb , I was able to do this,

	use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
	use Illuminate\Database\Query\Builder as QueryBuilder;
	...
	$sales = SaleInvoice::whereHas('payments', function (EloquentBuilder $query) {
		$query
			->where('payment_status',  '!=',  'closed')
			->whereIn('id', function (QueryBuilder $query) {
				$query
					->selectRaw('max(id)')
					->from('payments')
					->whereColumn('payable_id',  'sale_invoices.id');
			});
	});   

The problem is I use UUID for id fields and max('id') is not working and max('created_at') also doesn't seem to have any effect.

	SELECT * FROM ph_sale_invoices AS sv	
	LEFT JOIN ph_payments AS ps	
	ON ps.id = ( 
						SELECT ps1.id FROM ph_payments AS ps1 
									WHERE sv.id = ps1.payable_id 
									ORDER BY ps1.created_at 
									DESC LIMIT 1 
				) 
	WHERE ps.payment_status = 'closed';

As quoted by @scibuff , the above raw query seems to work.

I have no clue what is wrong here. Any help is much appreciated.

For me I created a HasOne relationship with ->latestOfMany() and it worked for a distant relation:

->whereHas('latestRelationshipOfMany.middleRelationship.endRelationship', fn($query) => $query->where('end_relationship_table.column_i_want', $value_i_want)