100% agree. It should work as expected from the beginning.
El mar, 8 oct 2024, 11:51, Per Nyfelt <per.nyf...@nordnet.se> escribió:
> 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 == s.item
> select w.name, w.price)
> groupby n.name
> select 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 == s.item
> groupby n.name
> select 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 == s.item
> select w.name, w.price)
> groupby n.name
> select n.name, sum(n.price) as price
> }
>
>
>
>
>
>
>
> El dom, 6 oct 2024 a las 10:26, Per Nyfelt (<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 = id
> 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 == s.item
> select 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 == s.item
> groupby w.name
> select 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
>
>
