Per Nyfelt created GROOVY-11491:
-----------------------------------
Summary: Add support for join and group by in ginq
Key: GROOVY-11491
URL: https://issues.apache.org/jira/browse/GROOVY-11491
Project: Groovy
Issue Type: Improvement
Affects Versions: 4.0.23
Reporter: Per Nyfelt
Ginq does not currently support expressions using join and groupby in
combination with aggregate functions as the following example shows:
import java.time.LocalDate
// Define two classes that we will use to create collections used in our ginq
query:
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
}
}
// Setup our two collections
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)
]
// "Append" the price to the sales events by inner joining Sales with Warehouse
// We want to summarize prices by name so currently we have to do it in 2 steps
// Step 1, join
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()
// Step 2: We can now use that query to groupby and sum i.e:
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()
// But it "should" be possible to do this with just one expression
// however, doing it all in one go does not work:
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
// Ginq thinks sum(w.price) refers to the Sales class when it is actually
referring to the
// Warehouse class
assert [['Orange', 22.0], ['Banana', 4.0]] == qSum.toList()
As this type of query is common in the SQL world, easy to read and understand I
think it makes sense to support this in ginq as well.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
