Question 243369 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Eloquent Where sum of relationship column is more than parent value

I’m trying to write a query scope in Laravel Eloquent to get records only where the sum of a records relationship values is more than a value stored in a parent column. So for example I have:

Table A

id total
1 50
2 100

Table B

id table_a_id total
1 1 10
2 1 20
3 1 10
4 1 20
5 2 10
6 2 20

So in this example, the query would return Table A’s record 1 but not 2, because record 1 has a total of 50 and the sum of Table B’s records related to it is 60, whereas it would not return Table A’s record 2 as the sum total of its related Table B records is 30, which is less than its total of 100.

I’ve tried withSum as that looked interesting, however that does not give a useable piece of data I can then use in a where query to check against the total of Table A.

I could get() the results and then filter the collection quite easily but I want to do it within the Query as it would be more efficient.

Any advice is appreciated! Many thanks

Tags:

2

Answers


  1. 0

    use this :

    $results = TableA::withSum('tableB', 'total') 
    ->whereHas('tableB', function (Builder $query) {
        $query->selectRaw('SUM(total) >= table_a.total');
    })
    ->get();

    or you can use joins :

    $results = TableA::join('table_b', 'table_a.id', '=', 'table_b.table_a_id')
    ->select('table_a.*', DB::raw('SUM(table_b.total) as total_b'))
    ->groupBy('table_a.id', 'table_a.total')
    ->havingRaw('SUM(table_b.total) >= table_a.total')
    ->get();
    Login or Signup to reply.
  2. 0

    It should be reminded that no matter how a query is optimized, there will be a point where it reaches business level complexity and can not be simplified any further.

    This could be that case where 1 – All sums must be calculated and 2 – Filter out which satisfies the condition.

    I’ve tried withSum as that looked interesting, however that does not
    give a useable piece of data I can then use in a where query to check
    against the total of Table A.

    You could’ve provided your code for estimation. But since you didn’t, I will post mine:

    $results = A::withSum('Bs', 'total')
    ->get()
    ->filter(function($item) {
        return $item->total > $item->Bs_sum_total;
    });

    I bet withSum will do the job just fine and optimal. But if you insist on NOT using Collection, I got you covered also:

    $results = A::withSum('Bs', 'total')
    ->havingRaw('Bs_sum_total > total')
    ->get();

    Which basically do the same thing, but at lower SQL level.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search