Hi Daniel,
I was obviously not phrasing my question well. Let me give it another go:
Given two list of objects:
import java.time.*
class Employee {
String mainSsn
String coSsn
String firstName
BigDecimal salary
LocalDate startDate
}
class Identifier {
String customerId
String lei
}
List employees = [
new Employee(mainSsn: '111', firstName: 'Rick', salary: 623.3,
startDate: LocalDate.parse('2012-01-01')),
new Employee(mainSsn: '222', coSsn: '444', firstName: 'Dan', salary:
515.2, startDate: LocalDate.parse('2013-09-23')),
new Employee(mainSsn: '555', coSsn: '555', firstName: 'Michelle',
salary: 611.0, startDate: LocalDate.parse('2014-11-15')),
]
List identifiers = [
new Identifier(customerId: '2', lei: '111'),
new Identifier(customerId: '3', lei: '222'),
new Identifier(customerId: '4', lei: '333'),
new Identifier(customerId: '5', lei: '444'),
new Identifier(customerId: '6', lei: '555'),
]
// To add customerId to employees I can do:
def result = GQ {
from e in employees
leftjoin mcid in identifiers on e.mainSsn == mcid.lei
leftjoin cocid in identifiers on e.coSsn == cocid.lei
select e.mainSsn, e.coSsn, e.firstName, e.salary, e.startDate,
mcid?.customerId as mainCustomerId, cocid?.customerId as coCustomerId
}
and get this nice output:
+---------+-------+-----------+--------+------------+----------------+--------------+
| mainSsn | coSsn | firstName | salary | startDate | mainCustomerId |
coCustomerId |
+---------+-------+-----------+--------+------------+----------------+--------------+
| 111 | | Rick | 623.3 | 2012-01-01 | 2
| |
| 222 | 444 | Dan | 515.2 | 2013-09-23 | 3 |
5 |
| 555 | 555 | Michelle | 611.0 | 2014-11-15 | 6 |
6 |
+---------+-------+-----------+--------+------------+----------------+--------------+
// But this is rather tedious and i would want something that is
equivalent to the .* construct in SQL. I.e:
def result = GQ {
from e in employees
leftjoin mcid in identifiers on e.mainSsn == mcid.lei
leftjoin cocid in identifiers on e.coSsn == cocid.lei
select e.*, mcid?.customerId as mainCustomerId, cocid?.customerId as
coCustomerId
}
This does not work but if there was some way to convert e to "whatever
construct that is needed to make it expand all the properties", lets
call it expand(). The I could do
def result = GQ {
from e in employees
leftjoin mcid in identifiers on e.mainSsn == mcid.lei
leftjoin cocid in identifiers on e.coSsn == cocid.lei
select expand(e), mcid?.customerId as mainCustomerId,
cocid?.customerId as coCustomerId
}
is there (or could there be) a type (class) that expand(e) could return
so it would give me the same result as when i explicitly specify each field?
Regards,
Per
On 6/20/25 15:47, Daniel Sun wrote:
Hi Per,
You can find the `leftjoin` usage here:
https://github.com/apache/groovy/blob/b046d1b2bcbbddd59ea3d6abdf5de24a671ce51a/subprojects/groovy-ginq/src/spec/test/org/apache/groovy/ginq/GinqTest.groovy#L763
Cheers,
Daniel Sun
On 2025/06/17 20:46:35 Per Nyfelt wrote:
Hi,
I ran into a a ginq "issue" today.
I have two list of rows where each key is the column name (actually
List<Row> but you can think about it as a List<Map>) collections that
looks like this:
employees: 3 obs * 5 variables
mainSsn coSsn firstName salary startDate
111 Rick 623.3 2012-01-01
222 444 Dan 515.2 2013-09-23
333 555 Michelle 611.0 2014-11-15
eln: 5 obs * 2 variables
customerId lei
2 111
3 222
4 333
5 444
6 555
I want to join employees with eln and add customerIs columns matching
mainSsn and CoSsn. Thinking SQL, I wanted to do something like this
def result =GQ {
from tin employees
leftjoin mcidin elnon t.mainSsn == mcid.lei leftjoin cocidin elnon t.coSsn
== cocid.lei select t.*, mcid?.customerId as'mainCustomerId', cocid?.customerId
as'coCustomerId' }
but this is not supported in ginq and I could not find any signs of support for
wildcards in the docs.
I found that could do this:
def result = GQ {
from t in table
leftjoin mcid in ssnCustomerId on t.mainSsn == mcid.lei
leftjoin cocid in ssnCustomerId on t.coSsn == cocid.lei
select t + mcid?.customerId+ cocid?.customerId
}
Which gives me a List of Lists but then i loose the column names. i.e.
merged: 3 obs * 7 variables
c1 c2 c3 c4 c5 c6 c7
111 444 Rick 623.3 2012-01-01 2 5
222 Dan 515.2 2013-09-23 3 null
333 555 Michelle 611.0 2014-11-15 4 6
Instead i had to do this:
def result =GQ {
from tin employees
leftjoin mcidin elnon t.mainSsn == mcid.lei leftjoin cocidin elnon t.coSsn
== cocid.lei select t.toMap() + [mainCustomerId: mcid?.customerId] +
[coCustomerId: cocid?.customerId]
}
(This relies on the fact that a matrix Row has a toMap() method)
ginq result content:
[{mainSsn=111, coSsn=, firstName=Rick, salary=623.3,
startDate=2012-01-01, mainCustomerId=2, coCustomerId=null},
{mainSsn=222, coSsn=444, firstName=Dan, salary=515.2,
startDate=2013-09-23, mainCustomerId=3, coCustomerId=5}, {mainSsn=333,
coSsn=555, firstName=Michelle, salary=611.0, startDate=2014-11-15,
mainCustomerId=4, coCustomerId=6}]
Which is could then easily transform back into a list of rows (actually
a matrix but you can think of it as a List<Map>
result matrix content:
merged: 3 obs * 7 variables
mainSsn coSsn firstName salary startDate mainCustomerId
coCustomerId
111 Rick 623.3
2012-01-01 2 null
222 444 Dan 515.2
2013-09-23 3 5
333 555 Michelle 611.0 2014-11-15
4 6
Has there been any discussions about supporting breaking up the select
objects into parts using wildcards and deemed it not feasible to
implement or did nobody have this problem before?
Something like this could perhaps be an alternative to wildcard syntax:
def result =GQ {
from tin employees
leftjoin mcidin elnon t.mainSsn == mcid.lei leftjoin cocidin elnon t.coSsn
== cocid.lei select toMap(t) + [mainCustomerId: mcid?.customerId,coCustomerId:
cocid?.customerId])
}
what do you think?
Best regards,
Per
