Re: Ginq enhancement?

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
>>