1

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

Matdragon
  • 169
  • 1
  • 7

2 Answers2

1

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.

Huy Phạm
  • 888
  • 9
  • 24
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();
Hammad Ahmed khan
  • 1,618
  • 7
  • 19
  • Hi there, thank you for your reply! Unfortunatetly the first just returns all records, and the second returns any that have an existing table_b row and requires the database strict config to be turned off (which i'd rather not do). The first looks like it should work, its odd that its not filtering anything out! – Matdragon Aug 29 '23 at 12:47
  • try the join method – Hammad Ahmed khan Aug 29 '23 at 12:54
  • The second unfortunately returns any that have an existing table_b row and requires the database strict config to be turned off (which i'd rather not do) – Matdragon Aug 29 '23 at 13:50