Eloquent with sub-select
I need to do something like this, and i'd like to figure out if I can do it in eloquent, so I get the benefit of the model..
select
*,
(select sum(d) from othertable where table.primarykey=othertable.foreignkey) as mysum
from table
where whereclause
order by table.primarykey
I'd like my record to consist of all the columns in table plus a column called mysum.
I've tried to insert an addSelect call in the but I can't get eloquent to generate valid syntax.
Can somebody suggest how this is to be done?
Yes you can do this with Eloquent.
Here an example with a orders and articles, you could for example retrieve all orders and the sum of the prices of the articles for each order.
Assuming you have defined the relationship in the Order model.
public function articles()
{
return $this->hasMany(Article::class);
}
You can retrieve the data from the orders table and the sum of the price of the articles.
$orders = Order::
withSum('articles', 'price')
->where(...)
->orderBy('id')
->get();
https://laravel.com/docs/10.x/eloquent-relationships#other-aggregate-functions
If you have a good working regular query, I suggest using it, but bind any parameters.
Eloquent converts to regular sql at runtime any way.
Please or to participate in this conversation.
