Re: Ginq: combining join and aggregate functions

100% agree. It should work as expected from the beginning.

El mar, 8 oct 2024, 11:51, Per Nyfelt <[email protected]> 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 <[email protected]>
> *Reply to: *"users-GSC0n/0aZo1d/SJB6HiN2Ni2O/[email protected]" <users-GSC0n/0aZo1d/SJB6HiN2Ni2O/[email protected]>
> *Date: *Monday, 7 October 2024 at 12:45
> *To: *"users-GSC0n/0aZo1d/SJB6HiN2Ni2O/[email protected]" <users-GSC0n/0aZo1d/SJB6HiN2Ni2O/[email protected]>
> *Subject: *Re: Ginq: combining join and aggregate functions
>
>
>
> You don't often get email from mario.ggar-Re5JQEeQqe9fmgfxC/sS/[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 == 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 (<[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 = 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
>
>