Re: Ginq: combining join and aggregate functions

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 <[email protected]>
Reply to: "[email protected]" <[email protected]>
Date: Monday, 7 October 2024 at 12:45
To: "[email protected]" <[email protected]>
Subject: Re: Ginq: combining join and aggregate functions


You don't often get email from [email protected]. 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 (<[email protected]<mailto:[email protected]>>) 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