Hi Ben, What is (x).* and (x).id? I mean, there is no table called x in the outer query, only a table called y. Is x a user-defined type?
2014-11-20 12:20 GMT+01:00 Ben Hood <[email protected]>: > Hi Lukas, > > I'm wondering what is the most idiomatic way to encode the following > fragment in JOOQ: > > SELECT (x).*, pg_try_advisory_xact_lock((x).id) AS locked > FROM ( > SELECT x > FROM queues AS x > WHERE context = 'foo' > ORDER BY id, priority > LIMIT 1 > ) AS y; > > I've tried to build the statement up from the middle, but I'm getting > snagged with my hacky way of using aliases: > > db.select(DSL.field("x")).from(QUEUES.as("x")). > where(QUEUES.CONTEXT.eq("foo")). > orderBy(QUEUES.ID, QUEUES.PRIORITY). > limit(1). > fetchOne(); > You're renaming QUEUES to "x", but then you're still using the QUEUES reference to dereference columns from. Try doing this: Queues x = QUEUES.as("x") And then x.CONTEXT.eq("foo") x.ID, x.PRIORITY > So JOOQ is probably doing the right thing by rendering this: > > select x from "public"."queues" as "x" where > "public"."queues"."context" = 'foo' order by "public"."queues"."id" > asc, "public"."queues"."priority" asc limit 1 offset 0 > > But the DB is also correctly telling me that my alias is bogus: > > Exception in thread "main" java.lang.RuntimeException: > org.postgresql.util.PSQLException: ERROR: invalid reference to > FROM-clause entry for table "queues" > Hint: Perhaps you meant to reference the table alias "x". > Position: 46 > In other words, precisely what I said ;-) -- 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.
