Howdy, all, am having a little trouble with an Eloquent query that uses multiple models.

The trouble lies in gathering one of the returned columns, which is an aggregate.

Tables:

Model1 -> Contains foreignID fields for model2_id and model_id.
Model2 -> Whose ID field will match to model1.model2_id.
Model3 -> Whose ID field will match to model1.model3_id.
Model4 -> Contains a foreignID field which will match to model1.id.

Model1 records match exactly one Model2 record, and one Model3 record.

There are zero or more Model4 records associated with Model1.

This works in SQL:

select 
    mdl1.id as Mdl1_ID, 
    mdl2.id as Mdl2_ID, 
    mdl2.name as Name, 
    format(mdl1.field3, 2) as Desired_Number, 
    count(mdl4.Model1_id) as Desired_Aggregate
from  
    Model1 mdl1  
    join Model2 mdl2 on mdl1.mdl2_id = mdl2.id 
    join Model3 mdl3 on mdl1.mdl3_id = mdl3.id 
    left join Model4 mdl4 on mdl4.mdl1_id = mdl1.id 
where 
    mdl3.id = '5'
group by
    mdl1.id 
order by
    Desired_Aggregate desc,
    Name
limit 5;

SQL Result

| Mdl1_ID | Mdl2_ID | Name | Desired_Number | Desired_Aggregate |

| ------- | ------- | ---------------------- | --------------- | ----------------- |

| 1234 | 567 | Homer Simpson | 25.27 | 3 |

| 1357 | 216 | Pliny the Elder | 105.00 | 2 |

| 4416 | 704 | M.C. 900 Foot Jesus | 12.44 | 1 |

| 8092 | 35 | Carl Reiner | 82.19 | 0 |

| 6113 | 228 | Jubal Harshaw | 15.05 | 0 |

The controller has 'use' statements for all models.

This works in a controller method, WITHOUT the aggregate "Desired_Aggregate" column as part of the returned collection:

return Model1::join('model2', 'model1.model2_id', '=', 'model2.id')
    ->join('model3', 'model1.model3_id', '=', 'model3.id')
    ->where('model3.id', '=', '5')
    ->get([
        'model1.id as model1_id',
        'model2.id as model2_id',
        'model2.name as Name',
        'model1.ttl as Desired_Number'
        ]);

After adding a leftJoin so the query can match Model1.id to Model4.model1_id, use of the the aggregate 'count(model4.model1_id)' in the get() method breaks the query, saying it's an unknown column.

What would be the eloquent version of that working SQL query?

I have tried various combinations of ->select, using DB::table instead the model, and other things, but have not gotten this to work.

I could probably do two queries and merge based on Model1's ID, but that seems inefficient. There has to be a way to do it in one pass, with the same result as the working SQL query.

Any illumination would be most appreciated. Thanks!