I have two tables: PayloadQuery (Id, ClientId, ActivationTime, Interval, Repeats) and PayloadResults (Id, PayloadQueryId, Result). To each query may belong either many results or none at all.
And I need to get a list of queries matching such conditions:
Code:
select distinct pq from PayloadResults res right join res.PayloadQuery pq
where ((pq.ActivationTime<=? or pq.ActivationTime is null) and pq.Client=?) and (
(pq.ActivationTimeis null and count(res) = 0) or
(pq.Interval is null and pq.Repeats is null and count(res) = 0) or
(pq.Interval is not null and pq.Repeats is null and datediff(minute, pq.ActivationTime, current_timestamp())/pq.Interval > count(res)) or
(pq.Interval is not null and pq.Repeats is not null and (datediff(minute, pq.ActivationTime, current_timestamp())/pq.Interval > count(res)) and count(res) < pq.Repeats))
The main problem in this draft is that each "count(res)" should relate to a specific query - count of PayloadResults belonging to this PayloadQuery.
I tried to add
Code:
group by pq having count(res) < pq.Repeats
but got ActiveRecordException saying: "
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
Can someone please explain me how to make this query correct and working?
Login
FAQ
Search