Re: Ginq enhancement?

Hi Per,

You can find the `leftjoin` usage here: https://github.com/apache/groovy/blob=
/b046d1b2bcbbddd59ea3d6abdf5de24a671ce51a/subprojects/groovy-ginq/src/spec/te=
st/org/apache/groovy/ginq/GinqTest.groovy#L763

Cheers,
Daniel Sun

On 2025/06/17 20:46:35 Per Nyfelt wrote:
> Hi,
>=20
> I ran into a a ginq "issue" today.
>=20
> I have two list of rows where each key is the column name (actually=20
> List<Row> but you can think about it as a List<Map>)=C2=A0 collections that=
=20
> looks like this:
>=20
> employees: 3 obs * 5 variables
> mainSsn=C2=A0=C2=A0=C2=A0 coSsn=C2=A0=C2=A0=C2=A0 firstName=C2=A0=C2=A0=C2=
=A0 salary=C2=A0=C2=A0=C2=A0 startDate
> 111=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 Rick=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 623.3 2012-01-01
> 222=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 444=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Dan=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0 515.2=C2=A0=C2=A0=C2=A0 2013-09-23
> 333=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 555 =C2=A0 =C2=A0=C2=A0=C2=A0 Michelle =C2=A0 =C2=A0=C2=A0=C2=A0 611.0=C2=A0=
=C2=A0=C2=A0 2014-11-15
>=20
> eln: 5 obs * 2 variables
> customerId=C2=A0=C2=A0=C2=A0 lei
>  =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 111
>  =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 3=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 222
>  =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 4=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 333
>  =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 5=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 444
>  =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 6=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 555
>=20
> I want to join employees with eln and add customerIs columns matching=20
> mainSsn and CoSsn. Thinking SQL, I wanted to do something like this
>=20
> def result =3DGQ {
>    from tin employees
>    leftjoin mcidin elnon t.mainSsn =3D=3D mcid.lei leftjoin cocidin elnon t=
.coSsn =3D=3D cocid.lei select t.*, mcid?.customerId as'mainCustomerId', coci=
d?.customerId as'coCustomerId' }
>=20
> but this is not supported in ginq and I could not find any signs of support=
 for wildcards in the docs.
>=20
> I found that could do this:
> def result =3D GQ {
>    from t in table
>    leftjoin mcid in ssnCustomerId on t.mainSsn =3D=3D mcid.lei
>    leftjoin cocid in ssnCustomerId on t.coSsn =3D=3D cocid.lei
>    select t + mcid?.customerId+ cocid?.customerId
> }
>=20
> 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
>=20
> Instead i had to do this:
>=20
> def result =3DGQ {
>    from tin employees
>    leftjoin mcidin elnon t.mainSsn =3D=3D mcid.lei leftjoin cocidin elnon t=
.coSsn =3D=3D cocid.lei select t.toMap() + [mainCustomerId: mcid?.customerId]=
 + [coCustomerId: cocid?.customerId]
> }
>=20
> (This relies on the fact that a matrix Row has a toMap() method)
>=20
> ginq result content:
> [{mainSsn=3D111, coSsn=3D, firstName=3DRick, salary=3D623.3,=20
> startDate=3D2012-01-01, mainCustomerId=3D2, coCustomerId=3Dnull},=20
> {mainSsn=3D222, coSsn=3D444, firstName=3DDan, salary=3D515.2,=20
> startDate=3D2013-09-23, mainCustomerId=3D3, coCustomerId=3D5}, {mainSsn=3D3=
33,=20
> coSsn=3D555, firstName=3DMichelle, salary=3D611.0, startDate=3D2014-11-15, =

> mainCustomerId=3D4, coCustomerId=3D6}]
>=20
> Which is could then easily transform back into a list of rows (actually=20
> a matrix but you can think of it as a List<Map>
>=20
> result matrix content:
> merged: 3 obs * 7 variables
> mainSsn=C2=A0=C2=A0=C2=A0 coSsn=C2=A0=C2=A0=C2=A0 firstName=C2=A0=C2=A0=C2=
=A0 salary=C2=A0=C2=A0=C2=A0 startDate mainCustomerId=C2=A0=C2=A0=C2=A0=20
> coCustomerId
> 111=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0 Rick=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 623.3=20
> 2012-01-01=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2 null
> 222=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 444=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Dan=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0=C2=A0 515.2=20
> 2013-09-23=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
3 =C2=A0=C2=A0 5
> 333=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 555=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Michelle=C2=A0=C2=A0=C2=A0=C2=A0 =C2=
=A0 611.0 2014-11-15=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0=20
>  =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 4 =
=C2=A0=C2=A0=C2=A0 6
>=20
> Has there been any discussions about supporting=C2=A0 breaking up the selec=
t=20
> objects into parts using wildcards and deemed it not feasible to=20
> implement or did nobody have this problem before?
>=20
> Something like this could perhaps be an alternative to wildcard syntax:
>=20
> def result =3DGQ {
>    from tin employees
>    leftjoin mcidin elnon t.mainSsn =3D=3D mcid.lei leftjoin cocidin elnon t=
.coSsn =3D=3D cocid.lei select toMap(t) + [mainCustomerId: mcid?.customerId,c=
oCustomerId: cocid?.customerId])
> }
>=20
> what do you think?
>=20
> Best regards,
>=20
> Per
>=20