Can I add the "with" query option to my model-directed requests?

Question

I have a query, I wrote a query that queries the data

with last_sent_at as (
    Select offer_id, lead_id, max(created_at) as sent_at
    From offer_history
    Group by offer_id, lead_id)

I need to connect it with laravel model system. So I have three tables: leads => history => offers I have a request Lead::with([..., 'offers'])->someFunction(?)->filters()->get();

I need to get the data from 'last_sent_at' but I don't know how to do it. I tried subquery but it was very slow

Answer 1

You can achieve this by setting up the history table as a pivot table, so the query will look like this.

$query = Lead::with([
             'history' => function($history) {
                $history->select(column names);
            },
            'history.offer' => function ($offer) {
              $offer => select(column names);
            }])->where('Your condition')
                 ->get();