Ginq enhancement?
Per Nyfelt <[email protected]> Tue, 17 Jun 2025 22:46:35 +0200
| Newsgroups | gmane.comp.lang.groovy.user |
|---|---|
| Organization | Alipsa HB |
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format.
--------------qK0asjhaSOMArlJOTQNiqP5U
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
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
--------------qK0asjhaSOMArlJOTQNiqP5U
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi,</p>
<p>I ran into a a ginq "issue" today.<br>
</p>
<p>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:<br>
</p>
<p>employees: 3 obs * 5 variables <br>
mainSsn coSsn firstName salary startDate <br>
111 Rick 623.3
2012-01-01<br>
222 444 Dan 515.2 2013-09-23<br>
333 555 Michelle 611.0 2014-11-15</p>
<p>eln: 5 obs * 2 variables <br>
customerId lei<br>
2 111<br>
3 222<br>
4 333<br>
5 444<br>
6 555</p>
<p>I want to join employees with eln and add customerIs columns
matching mainSsn and CoSsn. Thinking SQL, I wanted to do something
like this<br>
</p>
<pre
style="font-family:'JetBrains Mono',monospace;font-size:10.5pt;"><span
style="color:#cf8e6d;">def </span>result = <span
style="color:#c77dba;font-style:italic;">GQ </span>{
<span style="color:#cf8e6d;">from </span>t <span
style="color:#cf8e6d;">in </span>employees
<span style="color:#cf8e6d;">leftjoin </span>mcid <span
style="color:#cf8e6d;">in </span>eln <span style="color:#cf8e6d;">on </span>t.<span
style="color:#757a85;">mainSsn </span>== mcid.<span
style="color:#757a85;">lei
</span><span style="color:#757a85;"> </span><span
style="color:#cf8e6d;">leftjoin </span>cocid <span
style="color:#cf8e6d;">in </span>eln <span style="color:#cf8e6d;">on </span>t.<span
style="color:#757a85;">coSsn </span>== cocid.<span
style="color:#757a85;">lei
</span><span style="color:#757a85;"> </span><span
style="color:#cf8e6d;">select </span>t.*, mcid?.<span
style="color:#757a85;">customerId</span> as <span
style="color:#6aab73;">'mainCustomerId'</span>, cocid?.<span
style="color:#757a85;">customerId </span>as <span
style="color:#6aab73;">'coCustomerId'
</span>}
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<span style="color:#6aab73;"> </span>+ 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:
</pre>
<p></p>
<div style="background-color:#1e1f22;color:#bcbec4">
<pre
style="font-family:'JetBrains Mono',monospace;font-size:10.5pt;"><span
style="color:#cf8e6d;">def </span>result = <span
style="color:#c77dba;font-style:italic;">GQ </span>{
<span style="color:#cf8e6d;">from </span>t <span
style="color:#cf8e6d;">in </span>employees
<span style="color:#cf8e6d;">leftjoin </span>mcid <span
style="color:#cf8e6d;">in </span><span style="color:#cf8e6d;"></span>eln <span
style="color:#cf8e6d;">on </span>t.<span style="color:#757a85;">mainSsn </span>== mcid.<span
style="color:#757a85;">lei
</span><span style="color:#757a85;"> </span><span
style="color:#cf8e6d;">leftjoin </span>cocid <span
style="color:#cf8e6d;">in </span><span style="color:#cf8e6d;"></span>eln <span
style="color:#cf8e6d;">on </span>t.<span style="color:#757a85;">coSsn </span>== cocid.<span
style="color:#757a85;">lei
</span><span style="color:#757a85;"> </span><span
style="color:#cf8e6d;">select </span>t.toMap() + [<span
style="color:#6aab73;">mainCustomerId</span>: mcid?.<span
style="color:#757a85;">customerId</span>] + [<span
style="color:#6aab73;">coCustomerId</span>: cocid?.<span
style="color:#757a85;">customerId</span>]
}</pre>
</div>
<p>(This relies on the fact that a matrix Row has a toMap() method)<br>
</p>
<p>ginq result content:<br>
[{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}]<br>
</p>
<p>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> <br>
</p>
<p>result matrix content:<br>
merged: 3 obs * 7 variables <br>
mainSsn coSsn firstName salary startDate
mainCustomerId coCustomerId<br>
111 Rick 623.3
2012-01-01 2
null <br>
222 444 Dan 515.2
2013-09-23 3
5<br>
333 555 Michelle 611.0
2014-11-15 4
6<br>
</p>
<p>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?</p>
<p>Something like this could perhaps be an alternative to wildcard
syntax:</p>
<pre
style="font-family:'JetBrains Mono',monospace;font-size:10.5pt;"><span
style="color:#cf8e6d;">def </span>result = <span
style="color:#c77dba;font-style:italic;">GQ </span>{
<span style="color:#cf8e6d;">from </span>t <span
style="color:#cf8e6d;">in </span>employees
<span style="color:#cf8e6d;">leftjoin </span>mcid <span
style="color:#cf8e6d;">in </span>eln <span style="color:#cf8e6d;">on </span>t.<span
style="color:#757a85;">mainSsn </span>== mcid.<span
style="color:#757a85;">lei
</span><span style="color:#757a85;"> </span><span
style="color:#cf8e6d;">leftjoin </span>cocid <span
style="color:#cf8e6d;">in </span>eln <span style="color:#cf8e6d;">on </span>t.<span
style="color:#757a85;">coSsn </span>== cocid.<span
style="color:#757a85;">lei
</span><span style="color:#757a85;"> </span><span
style="color:#cf8e6d;">select toMap(</span>t) + [<span
style="color:#6aab73;">mainCustomerId</span>: mcid?.<span
style="color:#757a85;">customerId</span>, <span
style="color:#6aab73;">coCustomerId</span>: cocid?.<span
style="color:#757a85;">customerId</span>])
}
what do you think?
</pre>
<p></p>
<p>Best regards,</p>
<p>Per<br>
</p>
</body>
</html>
--------------qK0asjhaSOMArlJOTQNiqP5U--