[
https://issues.apache.org/jira/browse/GROOVY-11491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Daniel Sun resolved GROOVY-11491.
---------------------------------
Fix Version/s: 5.0.0-alpha-11
4.0.24
Assignee: Daniel Sun
Resolution: Fixed
> 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
> Assignee: Daniel Sun
> Priority: Major
> Fix For: 5.0.0-alpha-11, 4.0.24
>
>
> 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)
