CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-7
Lucid Polygon
Lucid Polygon

Reputation: 572

Laravel eloquent query with sum of values from another table on a condition

I have two tables. Transaction and allocation. one Transaction can have multiple allocation. Relationships defined as follows:

Inside Allocation Table

class Allocation extends Model
{
    public function transaction_fk(){
        return $this->belongsTo('App\Models\Transaction','transaction_id');
    }
}

Inside Transaction Table

class Transaction extends Model
{
    public function allocations() {
        return $this->hasMany('App\Models\Allocation');
    }
}

I need to query all the rows from the transaction table with a certain ID along with the sum of allocations for that transaction line with sum of allocation totals if transaction total is not equal to sum of allocation totals. Something along the lines of the following: 

Transaction::where('id',$id)->where('amount','!==','sum(allocations.amount)')->with('balance' as 'sum(allocations.amount)')->get();

This query does not work. What am I missing?

I was able to do it as a simple query which I looped through and made a second query and added to list. It gives me the correct result. As you can see this is long-winded and slower. I need to do it straight away while querying the database one time. 

 $transactions2 = Transaction::where('contact_type','supplier')
    ->where('contact_id',$ehead->supplier_id)
    ->get();

    $transactions = [];
    foreach ($transactions2 as $item) {
       $sum = Allocation::where('transaction_id',$item['id'])->get()->sum('amount');
       if($item['amount'] !== $sum){
        $item->unallocated_amount = $sum;
        array_push($transactions, $item);
       }

    }

Upvotes: 3

Views: 1940

Answers (1)

Mehdi
Mehdi

Reputation: 756

You have to use groupBy and aggregation functions if you want to do this in a single query.

$items = Transaction::query()
            ->join('allocations', 'transactions.id', '=', 'allocations.transaction_id')
            ->groupBy('transactions.id')
            ->having('transactions.amount', '<>', 'sum(allocations.amount)')
            ->whereIn('transactions.id', $transactions2)
            ->select('transactions.*')
            ->get();

Upvotes: 0

Related Questions