Hi Lukas,
Thank you for your replies. Definitely returning a Map<CountryRecord,
List<CityRecord>> would be excellent.
In the meantime I've been playing around and have come up with this
solution which is much nicer. It maps them into a transfer object like
this:
List<Record> result = create.select().from(COUNTRY)
.leftOuterJoin(CITY).onKey().orderBy(COUNTRY.NAME.asc(),
CITY.NAME.desc()).fetch();
String lastKey = "";
for (Record record : result) {
CountryDTO dc = null;
if (!lastKey.equals(record.getValue(COUNTRY.CODE))) {
dc = new CountryDTO();
dc.setCode(record.getValue(COUNTRY.CODE));
dc.setName(record.getValue(COUNTRY.NAME));
dc.setCities(new ArrayList<CityDTO>());
dcList.add(dc);
lastKey = dc.getCode();
} else {
dc = dcList.get(dcList.size() - 1);
CityDTO dCity = new CityDTO();
dCity.setName(record.getValue(CITY.NAME));
dc.getCities().add(dCity);
}
}
I'm fairly happy with this. What I then pass to the view is easy to
iterate, and it's all fetched in a single select. I think that's a "win"!
All the best,
Andrew.
On Saturday, 30 March 2013 05:48:40 UTC+11, Lukas Eder wrote:
>
> Hi Andrew,
>
> I've given this some more thought...
>
> 2013/3/27 Andrew Myers <[email protected] <javascript:>>:
> > [...]
> > To achieve someting similar I was able to do:
> >
> > Map<String, List<CountryRecord>> ctry =
> > result.intoGroups(COUNTRY.CODE, CountryRecord.class);
> > Map<String, List<CityRecord>> cities =
> > result.intoGroups(COUNTRY.CODE, CityRecord.class);
> > for (Map.Entry<String, List<CountryRecord>> entry :
> > ctry.entrySet()) {
> > CountryRecord cr = entry.getValue().get(0);
> > // print country stuff
> >
> > List<CityRecord> thisCountriesCities =
> > cities.get(cr.getCode());
> > for (CityRecord c : thisCountriesCities) {
> > // print city stuff
> > }
>
> The problem that is most apparent here is the fact that at some point,
> while constructing the query, you've had all the information about the
> table types that you were selecting from. In fact, you have created a
> product of <R1> = COUNTRY and <R2> = CITY. While using the jOOQ API,
> this information is lost, and all you're left with is a generic Record
> type with an unknown set of Field<?>
>
> It would be quite nice, if jOOQ 3.0's row value expression typesafety
> could be leveraged to produce something like "nested records". In your
> case, each result record could have the type: Record2<CountryRecord,
> CityRecord>. Nesting records like this could then help grouping them
> by country, producing a Map<CountryRecord, List<CityRecord>> rather
> than more generic types. Also, the resulting Record2 records would
> really hold references to CountryRecord and CityRecord instances,
> while at the same time being compatible with the current, "flat" API
>
> I have registered #2360 for this idea:
> https://github.com/jOOQ/jOOQ/issues/2360
>
> Cheers
> Lukas
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
