Level 55
@chrisxhart your query make no sence. transactions_sum_amount is sum of all amounts. it can't be less then any of amount.
Also you can't use having statement without group statement (withSum works via subquery without grouping)
May 15, 2021
8
Level 16
Query count aggregate with sum
Hi all, trying to write a query below and saying the transactions_sum_amount isn't a column.
Context: A goal has many transactions, amount is per transaction. A goal also has an amount.
// Query throws an error
request()->user()->goals()->withSum('transactions', 'amount')->having('transactions_sum_amount', '>=', 'amount')->count()
// This query works as expected
request()->user()->goals()->withSum('transactions', 'amount')->having('transactions_sum_amount', '>=', 'amount')->get()
It looks like the count() isn't actually selecting the transactions_sum_amount column (as it only selects the count) for it to compare against. Anyway around this? Thanks.
Edit: How should I query against a computed aggregate if not this way?
Level 16
Thanks for the response, transaction has an amount column, goal also has an amount column. I'm comparing the sum of all transaction amounts to the goal amount.
The having works with the second query, if I replace having with where, it throws the invalid column name exception.
Level 16
So what it's doing here is comparing the calculated column to literal string "amount", but I want it to compare against goal->amount
select
` goals `.*,
(
select
sum(` transactions `.` amount `)
from
` transactions `
where
` goals `.` id ` = ` transactions `.` goal_id `
) as ` transactions_sum_amount `
from
` goals `
where
` goals `.` user_id ` = 1
and ` goals `.` user_id ` is not null
having
` transactions_sum_amount ` < 'amount'
order by
` name ` asc
Level 27
Have you tried using havingRaw instead?
Level 16
Yes, that fixed the second query, thanks.
->withSum('transactions', 'amount')->havingRaw('transactions_sum_amount >= amount')
Level 39
if I replace having with where
Having works after a 'group' operation
Where works 'directly'
You can compare with goal amount, but you have to have a 'link' between your two sub queries and the link here the link should be 'transaction id' in goals table. Below I gave you the general example, you have to adapt it with what you want, per user, ...
sql 1: select id,sum(...) AS my_sum FROM transactions GROUP BY id
et main sql
sql2 : select .... FROM goals r2 JOIN ( sql 1 ) r1 ON r2.transaction_id=r1.id WHERE r1.my_sum < r2.amount
Level 16
I see what you're saying, this should allow me to select the count solving the issue in my first query, thanks. Now I just have to figure out how to convert this to the query builder, lol.
Level 39
$sql1=...
$sql2=...
\DB:select("$sql2");
Please or to participate in this conversation.