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(", ");
}
}
return
context
.fetch
(
"select * from crosstab('"
+ raw.getSQL(ParamType.INLINED).replace("'", "''") +
"',
'"
+ crossTabValues.getSQL(ParamType.INLINED).replace(
"'", "''")
+ "') as ct(" + ctList.toString() + " )"
);
}
}
The function takes a pair of select queries, corresponding to the two
parameters of cross tab. So, for example, here's one from our system
(abbreviated for clarity, so it probably doesn't compile now). The example
below cross-tabulates participants in the delivery of one of our products.
Each participant is in one or more cohorts (DELIVERY_PARTICIPANTS) and this
query shows you a table of which participants are enrolled in which cohorts
(defined in DELIVERY_COHORTS).
DSLContext context = ...
Select<?> raw =
DSL.select
(
DSL.concat(DELIVERY_PARTICIPANTS.DELIVERY_ID, DELIVERY_PARTICIPANTS.
EMAIL),
DELIVERY_PARTICIPANTS.DELIVERY_ID,
DELIVERY_PARTICIPANTS.EMAIL,
DELIVERY_PARTICIPANTS.COHORT,
DSL.val("Yes").as("Enrolled")
)
.from(DELIVERY_PARTICIPANTS)
.where(DELIVERY_PARTICIPANTS.DELIVERY_ID.eq(deliveryID))
.orderBy(1, 2, 3);
Select<?> crossTabValues =
DSL.select(DELIVERY_COHORTS.COHORT) //these are is known to be distinct
.from(DELIVERY_COHORTS)
.orderBy(DELIVERY_COHORTS.DISPLAY_ORDER);
return PostgresWrapper.pivot(context, raw, crossTabValues);
which gives about the same complexity for usage as the actual Postgres "in
raw sql" version, except that the PostgresWrapper.pivot() calculates the
'ct' part for you.
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.
Please feel free to use it if you want to.
Cheers,
Joe
--
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.
