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

Reply via email to