Thanks for your time.
I confess Mr Google found me the postgres specific? sql I showed (but it
works like a charm). I had seen the generated alias and used the error
message to show that with the correct reference my attempted jOOQ would
have worked.
I came to this in the end
if (psetRec == null) {
System.out.println("didn't find set for these probands:
" + idList.toString());
ctx.insertInto(PROBANDSET)
.set(PROBANDSET.ID, newsetId)
.set(PROBANDSET.NAME, idList.toString())
.set(PROBANDSET.PROBANDS,
PostgresDSL.array(select(PERSON.ID).from(PERSON).where(PERSON.NAME.in(idList)).orderBy(PERSON.ID)))
.execute();
psetRec =
ctx.selectFrom(PROBANDSET).where(PROBANDSET.ID.equal(newsetId)).fetchOne();
}
return psetRec;
I wondered if a "Field<UUID[]>" was the answer, but needed to get on
loading data to see if my schema will do what we need.
Not thrilled with forcing PostgresDSL into the picture but at this stage
I'm just learning jOOQ and most of what I'm writing currently will be
thrown away.
To explain why I'm using arrays: I'm dealing with power sets of a give
list of people. N people generate 2^N subsets. That itself expands very
rapidly but you can imagine how fast the set-member table would explode
with a record for each member of each subset. There's a practical upper
bound of my implementation of course - in fact we have sets too large
even for this optimization (2^50 = 1,125,899,906,842,624 subsets) - but
I hope to handle 20's.
Thanks for your help. I'll try the explicit alias but the table needing
the alias has but the id column.
On 03/15/2017 04:02 PM, Lukas Eder wrote:
Hi Rob,
Thanks for your enquiry. Interesting! Can you point me to the doc page
where this ORDER BY clause is documented, I somehow cannot seem to
find it. I wasn't aware of this syntax possibility.
The error you're getting is because jOOQ always generates an automatic
alias (in your case "alias_49688522") for your derived tables, because
that's a syntactic requirement in most databases. In that sense, the
"person" table ceases to exist syntactically outside of your derived
table. So, neither your ARRAY_AGG() operation, nor your ORDER BY
clause can make use of the qualified PERSON.ID <http://PERSON.ID>
column. You have two options:
1) Use an unqualified reference to the column (e.g. using plain SQL)
2) provide an explicit alias to your derived table: "person", e.g. by
using table(Select).as(PERSON), or select.asTable(PERSON)
Hope this helps,
Lukas
2017-03-13 23:58 GMT+01:00 Rob Sargent <[email protected]
<mailto:[email protected]>>:
I'm having a hard time constructing an ordered array using version
3.9 on postgres 9.6. I can't seem to get the equivalent of either
of these approaches
select (person.id <http://person.id> order by person.id
<http://person.id>) from person where person.name
<http://person.name> in (<list of names>);
select array_agg(s.id <http://s.id>) from (select p.id
<http://p.id> from seg.person as p where p.name
<http://p.name> in (<list of names>) order by p.id
<http://p.id>) as s;
This compiles
ctx.insertInto(PROBANDSET)
.set(PROBANDSET.ID <http://PROBANDSET.ID>, newsetId)
.set(PROBANDSET.NAME <http://PROBANDSET.NAME>,
pedfile.getName())
.set(PROBANDSET.PROBANDS,
ctx.select(arrayAgg(PERSON.ID
<http://PERSON.ID>)).from(ctx.select(PERSON.ID
<http://PERSON.ID>).from(PERSON).where(PERSON.NAME.in
<http://PERSON.NAME.in>(idList)).orderBy(PERSON.ID
<http://PERSON.ID>)))
.execute();
but generates a runtime sql message
org.jooq.exception.DataAccessException: SQL [insert into
"seg"."probandset" ("id", "name", "probands") values (?, ?,
(select array_agg("seg"."person"."id") from (select
"seg"."person"."id" from "seg"."person" where
"seg"."person"."name" in (?, ?, ?, ?, ?, ?, ?, ?) order by
"seg"."person"."id" asc) as "alias_49688522"))]; ERROR:
missing FROM-clause entry for table "person"
Position: 93
and I can't find the right spot to add another '.from(PERSON)'.
Any help appreciated
--
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]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
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]
<mailto:[email protected]>.
For more options, visit https://groups.google.com/d/optout.
--
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.
