O.K. So I'm having a couple of issues trying to flesh these suggestions out.
Two main things: (1) I couldn't find DSL.list(QueryPart). There is DSL.listAgg, but that doesn't seem like the right thing. My other attempt at using the templating as per your suggestion ended up quoting the entire contents of the ct() function call as one string. So, I kind of gave up. Ff there's an obvious mistake I'm making, please let me know. (2) The other thing I'd like to do is grab the resultQuery, and rather than just fetch from it, I'd like to join against it making new query. Is that even possible? What I think I need to do is somehow make a TableLike out of the resultQuery value, but I can't find a way forward there. I have workarounds to both of these (the workaround for (1) is just to leave things as they are, and the workaround for (2) is to make very complicated raw and crosstabValues queries, which only works sometimes). Any insight would be appreciated, but I don't want to take up too much of your time. Cheers, Joe On Wednesday, August 26, 2015 at 6:39:30 PM UTC+10, Lukas Eder wrote: > > Thank you very much for sharing this, Joe - very interesting approach > indeed! > > I'll comment on some parts, inline > > 2015-08-25 13:18 GMT+02:00 <[email protected] <javascript:>>: > >> >> >> On Wednesday, April 16, 2014 at 6:41:23 PM UTC+10, Ben Hood wrote: >>> >>> Hey Lukas, >>> >>> I was wondering if you've come across an example of using crosstabs in >>> Postgres with JOOQ? >>> >>> Cheers, >>> >>> Ben >>> >> >> >> For what it's worth, the following utility function is what I use to make >> use of crosstab combined with JOOQ. >> >> >> public class PostgresWrapper >> { >> >> public static Result<Record> pivot(DSLContext context, Select<?> raw, >> Select<?> crossTabValues) >> { >> List<Field<?>> resultFields = new ArrayList<Field<?>>(); >> >> //The result will contain all but the last two columns for the >> raw fields. >> >> Field<?> []rawFields = raw.fields(); >> for (int i = 0; i < rawFields.length - 2; i++) >> { >> resultFields.add(rawFields[i]); >> } >> >> //And then one column for each cross tab value specified >> Result<?> crossTabHeadings = context.fetch(crossTabValues); >> for (Record r : crossTabHeadings) >> { >> resultFields.add >> ( >> DSL.field >> ( >> r.getValue(0).toString(), >> rawFields[rawFields.length - 2].getDataType(context. >> configuration()) >> ) >> ); >> } >> >> //And postgres requires that the names of the resultant fields >> be specified >> // explicitly, using 'ct' <"Name of Field", type> pairs... >> StringBuffer ctList = new StringBuffer(); >> for (int i = 0; i < resultFields.size(); i++) >> { >> ctList.append >> ( >> "\"" + resultFields.get(i).getName() + "\" " >> + resultFields.get(i).getDataType(context. >> configuration()) >> .getTypeName(context. >> configuration()) >> ); >> >> if (i < resultFields.size() - 1) >> { >> ctList.append(", "); >> } >> } >> > > Perhaps, you could use DSL.list(QueryPart...) here. It will construct a > QueryPart that models a comma-separated list of other QueryParts. You can > then use ordinary DSLContext.renderInlined() API to construct the SQL. > Perhaps this makes things simpler...? > > >> >> >> return >> context >> .fetch >> ( >> "select * from crosstab('" >> + raw.getSQL(ParamType.INLINED).replace("'", "''" >> ) + "', '" >> + crossTabValues.getSQL(ParamType.INLINED). >> replace("'", "''") >> + "') as ct(" + ctList.toString() + " )" >> ); >> } >> } >> > > That looks fine. Alternatively, you could use jOOQ's plain SQL templating > to let jOOQ do the string escaping. Something along the lines of > > context.fetch( > resultQuery( > "select * from crosstab({0}, {1}) as ct({2})", > val(raw.getSQL(ParamType.INLINED)), > > val(crossTabValues.getSQL(ParamType.INLINED)), > > ctList // If you construct it using DSL.list() > > ) > ); > > > >> Any feedback on this approach would be appreciated. I couldn't work out a >> way to make it more type-safe, and I'm not sure that is possible. >> > > I doubt that you can do much more for a PIVOT query. We're facing the same > issue with jOOQ's built-in Oracle / SQL Server PIVOT support. It's just not > easy to produce type safe Record[N] types for all the possible permutations > of pivot columns. Besides, a PIVOT operation might quickly produce more > than jOOQ's 22 columns. > > In any case, thanks again for your display. We'll certainly take some > inspiration when implementing https://github.com/jOOQ/jOOQ/issues/3190 > > 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/d/optout.
