Eloquent Model query results differently to same plain SQL query (using withCount, whereHas & having)

I have an invoice models which hasMany payments modes, now i want tho collect all Invoices which are paid. For that i need to compare the invoice->totals (=400$) with the sum(payment->amount).

$invoices = Invoice::withCount([
                'payments as paymentsSum' => function($query) {
                    $query->select(DB::raw('SUM(amount)'));
                }
            ])->whereHas('payments', function ($query) {
                $query->completed();
            })
            ->having('paymentsSum','<',400) //$, for testing purpose
            ->get();

This returns me invoices even if the invoice->totals is greater than 400$.

If i use toSql() instead of get() and copy the raw query into phpmyadmin i get only invoices back, which are smaller than 400$, as exptected.

Is there any hidden processing after the raw sql query when using the eloquent model?

bobbybouwmann

6 years ago

Level 88

Can you post the raw query here?

1 like

tobias.b

6 years ago

Level 1

Thanks for your reply! Sure, that works perfect in mysql but not in eloquent queries (showing me invoices having > 400$ as well)

select `invoices`.*,
(select SUM(amount) from `payments` where `invoices`.`id` = `payments`.`invoice_id`) as `paymentsSum`
from `invoices`
where exists (select * from `payments` where `invoices`.`id` = `payments`.`invoice_id` and `status` = "complete")
having `paymentsSum` < 400

Please or to participate in this conversation.