Constraining main laravel query by its eager-loaded relationship

I have an eloquent model (Tenancy) which has a one-to-many relationship with a model 'RentPeriod', and I am trying to perform (with Eloquent) the equivalent of a left join to return a single record (and single field) from the 'RentPeriods' table, with which I can then filter the outer query.

Basically, the RentPeriods table has a "due_date" field, which I would like to use in a where clause on the main query below

$tenancies = Tenancy::with(['rent_periods' =>

function ($query) {

        $query->select('tenancy_id', 'due_date')
            ->where('due_date', '>=', $today)
            ->orderBy('id', 'asc');

    }])
    

        ->where('due_date', '>', $today)
        ->paginate(20);

Does anyone know how I can get this to work?

36864

8 years ago

Level 13

https://laravel.com/docs/5.5/eloquent-relationships#querying-relationship-existence

Last topic:

If you need even more power, you may use the whereHas and orWhereHas methods to put "where" conditions on your has queries. These methods allow you to add customized constraints to a relationship constraint, such as checking the content of a comment:
// Retrieve all posts with at least one comment containing words like foo%
$posts = Post::whereHas('comments', function ($query) {
   $query->where('content', 'like', 'foo%');
})->get();

So, you should be able to do something similar with your "due_date" field.

tykus

8 years ago

Best Answer

Level 104

If you want the constraint and the eager-loaded relations, then you need to do both a with and whereHas using the same constraint. You could either create an anonymous function to pass to both the with and whereHas methods or add a query scope to your model to encapsulate that logic.

1 like

jlrdw

8 years ago

Level 75

What is funny in a way is I came from enterprise java (medium size trucking co) writing and maintaining logistics software including accounts receivable and accounts payable. I never even heard the word encapsulate until seeing it on some php forums. Somehow I made good money programming servlets, jsp, and ejb's without words like that.

Oh eager load just means when needed.

Edit: https://stackoverflow.com/questions/25029465/whats-the-difference-between-abstraction-and-encapsulation

Second answer down.

tykus

8 years ago

Level 104

@jldrw I'm am jealous of all the people who don't know you.

jlrdw

8 years ago

Level 75

@tykus your fancy terms amaze me. Shows how smart you are. Very impressive.

Please or to participate in this conversation.