This compiles (heard that before?):
ctx.insertInto(PROBANDSET)
.set(PROBANDSET.ID, newsetId)
.set(PROBANDSET.NAME, pedfile.getName())
.set(PROBANDSET.PROBANDS,
ctx.select(arrayAgg(PERSON.ID)).from(ctx.select(PERSON.ID)
.from(PERSON)
.where(PERSON.NAME.in(idList))
.orderBy(PERSON.ID).asTable("id")))
.execute();
but of course arrayAgg(PERSON.ID) isn't found at runtime. Not sure how
to fake a field as the arrayAgg() parameter.
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.
