Compare sum of relational column

Something like this ?

$countA = $model->relations()->where('column', '=', 'a')->count();
$countB = $model->relations()->where('column', '=', 'b')->count();

$countA < $countB ...

@squiaios No, I have 1M records, I need to compare parent column value and the sum of a relational column by query

Hi @shineraj

There is no solution that won't internally use joins, although if what you mean is just using Eloquent and no Query Builder joins, you might try something like this:

Post::has('comments', '>=', DB::raw('posts.column_to_compare'))->get();

Of course I'm using Post and Comments and a >= operator as an example in this case as you didn't mentioned any models or the type of comparison that you want to do.

Also, if you want to get the relationship count as a field too, you can do:

Post::withCount('comments')
    ->has('comments', '>=', DB::raw('posts.column_to_compare'))
    ->get();

Hope this works for you!

Hi @marianomoreyra ,

I like your example.

my exact case is :

User Model has column "amout_to_be_paid" & User has multiple payments in Payment model.

I need to compare if the user paid the amount in full or not.

I wrote a Hasmany relation From User to Payment.

In this case what is your idea to resolve this ?

Please not i have 1M records & this is a case of filter to dril down user list to fully paid & not paid!

Hi @shineraj, you can achieve this using Nested WHERE Clause in the Eloquent Query.

use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\DB;
use App\Model\User;

$fully_paid_users = User::where(function (Builder $query) {
		return $query->selectRaw('SUM(`amount`)')
			->from('payments')
			->whereColumn('user_id', 'users.id');
	}, DB::raw('amount_to_be_paid'))
	->get();

This works only on Laravel 7.x & above

Documentation

I'm sorry @shineraj

I got mislead by a previous reply that talked about counts, but you needed sums, in which case my previous solution won't work.

In addition to @kingmaker_bgp solution, you can try with other options to get only those that are fully paid, and in another query those that are not.

fully paid

User::query()
    ->addSelect(['paid' => App\Payment::selectRaw('SUM(amount)')
        ->whereColumn('user_id', 'users.id')])
    ->having('amount_to_be_paid' , '=', DB::raw('paid'))
    ->get();

and for those with pending payments

User::query()
    ->addSelect(['paid' => App\Payment::selectRaw('SUM(amount)')
        ->whereColumn('user_id', 'users.id')])
    ->having('amount_to_be_paid' , '>', DB::raw('paid'))
    ->get();

Both are similar solutions, so it's just a matter of taste! :)