Yes, nested is slightly cleaner but doing aggregation functions on items not
part of the groupby is common in sql and would make ginq more intuitive to use
imho. i.e.
Instead of
def qSum = GQ {
from n in (
from s in sales
join w in warehouse on w.id<http://w.id/> == s.item
select w.name<http://w.name/>, w.price)
groupby n.name<http://n.name/>
select n.name<http://n.name/>, sum(n.price) as price
}
which would be equal to the sql
select n.name, sum(n.price) as price from (
select w.name, w.price
from sales s
join warehouse w on w.id= s.item
) n
group by n.name
I think it would make sense to support
def qSum = GQ {
from s in sales
join w in warehouse on w.id<http://w.id/> == s.item
groupby n.name<http://n.name/>
select n.name<http://n.name/>, sum(n.price) as price
}
which would equal the sql
select w.name, sum(w.price) as price from
from sales s
join warehouse w on w.id = s.item
groupby w.name<http://n.name/>
I submitted a feature request for this here:
https://issues.apache.org/jira/projects/GROOVY/issues/GROOVY-11491
From: Mario Garcia <mario.g...@gmail.com>
Reply to: "users@groovy.apache.org" <users@groovy.apache.org>
Date: Monday, 7 October 2024 at 12:45
To: "users@groovy.apache.org" <users@groovy.apache.org>
Subject: Re: Ginq: combining join and aggregate functions
You don't often get email from mario.g...@gmail.com. Learn why this is
important<https://aka.ms/LearnAboutSenderIdentification>
I guess you can create a nested statement and do it all at once
def qSum = GQ {
from n in (
from s in sales
join w in warehouse on w.id<http://w.id/> == s.item
select w.name<http://w.name/>, w.price)
groupby n.name<http://n.name/>
select n.name<http://n.name/>, sum(n.price) as price
}
El dom, 6 oct 2024 a las 10:26, Per Nyfelt
(<p...@alipsa.se<mailto:p...@alipsa.se>>) escribió:
I figured out a workaround i.e. to do it in two steps:
def q = GQ {
from s in sales
join w in warehouse on w.id == s.item
select w.name, w.price
}
assert [['Orange', 11.0], ['Orange', 11.0], ['Banana', 4.0]] == q.toList()
def q2 = GQ {
from w in q
groupby w.name
orderby w.name in desc
select w.name, sum(w.price)
}
assert [['Orange', 22.0], ['Banana', 4.0]] == q2.toList()
I submitted a feature request in Jira to support use of join, groupby and
aggregate functions together.
Regards,
Per
On 10/5/24 19:16, Per Nyfelt wrote:
Hi, I am trying to learn Ginq and are having problems with joining an
aggregation functions.
The following example illustrates the issue:
import java.time.LocalDate
class Warehouse {
int id
String name
Double price
int stock
Warehouse(int id, String name, Double price, int stock) {
this.id<http://this.id/> = id
this.name<http://this.name/> = name
this.price = price
this.stock = stock
}
}
class Sales {
LocalDate date
int item
Sales(LocalDate date, int item) {
this.date = date
this.item = item
}
}
List<Warehouse> warehouse = [
new Warehouse(1, 'Orange', 11, 2),
new Warehouse(2, 'Apple', 6, 3),
new Warehouse(3, 'Banana', 4, 1),
new Warehouse(4, 'Mango', 29, 10)
]
List<Sales> sales = [
new Sales(LocalDate.of(2024, 5, 1), 1),
new Sales(LocalDate.of(2024, 5, 2), 1),
new Sales(LocalDate.of(2024, 5, 3), 3)
]
def q = GQ {
from s in sales
join w in warehouse on w.id<http://w.id/> == s.item
select w.name<http://w.name/>, w.price
}
// so far so good, the following works:
assert [['Orange', 11.0], ['Orange', 11.0], ['Banana', 4.0]] == q.toList()
// now try to summarize by name
def qSum = GQ {
from s in sales
join w in warehouse on w.id<http://w.id/> == s.item
groupby w.name<http://w.name/>
select w.name<http://w.name/>, sum(w.price)
}
// Fails with Exception evaluating property 'price' for groovy.lang.Tuple2,
// Reason: groovy.lang.MissingPropertyException: No such property: price for
class: Sales
assert [['Orange', 22.0], ['Banana', 4.0]] == qSum.toList()
So for some reason ginq thinks it will find the price property in the Sales
class instead of in the Warehouse class so w.price here is not understood. What
is the right way to write it?
Regards,
Per
