Laravel 3: Joining and getting sum of two tables, Is there a better way?

Question

I'm trying to get columns of a table while getting sum of another related table (I'm using a MySQL database). Tables are Advantages and Conversions, Advantages has a conversionID column to join. They are all related via models. I first tried to achieve this with Eloquent method, but I could not succeed, so I came up with this Fluent method, which is working fine:

DB::table('conversions')
    ->join('advantages','advantages.conversionID','=','conversions.id')
    ->where('conversions.used','=',0)
    ->group_by('conversions.id')
    ->get(array(
        'conversions.*',
        DB::raw('SUM(advantages.point) as totaladvantage')
      ))

I guess the query describes how the columns are and what I want to achieve.

So my question is: Is there a more efficient way to achieve this? Using DB::raw for this seemed weird to me, and sum() method only returns sum of the columns. This is the almost only place where I wrote both fluent and raw query in my project, so that made me think if I'm missing something.

Thanks in advance

Answer 1

You can call aggregate methods through fluent

DB::table('conversions')
    ->join('advantages','advantages.conversionID','=','conversions.id')
    ->where('conversions.used','=',0)
    ->group_by('conversions.id')
    ->select(array(
        'conversions.*',
        'advantages.point as totaladvantage')
      )
    ->sum('advantages.point')

Havent tested this, so let me know how you get on