I read that article--I do understand the ordering of operations, but I don't understand why or how postgres is complaining when the SQL that is being generated is SQL that postgres itself will run. Furthermore, I'm pretty sure that ordering by an alias is entirely doable in JPQL.. so I'm not sure why it's choking here. I'm actually using the JPA criteria API to do this.
Here's a test case that should summarize my situation:
There are persons, and each Person has Stuff, which is their own local version of other Things.
peoples' Stuff is one to many to Things. I need to count the distinct things based on some other predicates, while ordering by the peoples' names. So essentially I'm selecting from table A based on an ordering of fields from table C (through table B, stuff).
Code:
CREATE TABLE thing (
id serial PRIMARY KEY
);
CREATE TABLE person (
id serial PRIMARY KEY,
first_name varchar(255),
last_name varchar(255),
username varchar(255)
);
CREATE TABLE stuff (
id serial PRIMARY KEY,
thing_id int NOT NULL CONSTRAINT "stuff+thing.fk" REFERENCES thing(id) ON DELETE CASCADE,
person_id int NOT NULL CONSTRAINT "stuff+person.fk" REFERENCES person(id) ON DELETE CASCADE
);
This is how I'm doing the query:
Quote:
em.createQuery( q.multiselect(thing, person, sortExp) // sortExp being the expression equivalent to the case statements above
.where(predicates)
.distinct(true)
.orderBy(cb.asc(sortExp)))
The SQL generated is roughly:
Code:
select distinct thing.id,
case
when (person.first_name is null) and (person.last_name is null) then lower(person.username)
when (person.first_name is not null) and (person.last_name is not null) then lower(((person.first_name||'')||person.last_name))
else lower(coalesce(person.first_name, person.last_name)) end as col_3_0_
from thing
left outer join stuff on thing.id=stuff.thing_id
left outer join person on stuff.person_id=person.id
order by
case
when (person.first_name is null) and (person.last_name is null) then lower(person.username)
when (person.first_name is not null) and (person.last_name is not null) then lower(((person.first_name||'')||person.last_name))
else lower(coalesce(person.first_name, person.last_name)) end
desc
If the generated SQL just used the alias col_3_0_ that it's provided.. instead of re-creating the whole case... I'm pretty sure this would function fine. Again, this query works in postgres CLI, so I have no idea why postgres is complaining about it.
This definitely is postgres choking, though I wish I knew why the postgres that hibernate is using acts differently than my CLI postgres. I'm using postgres 9.3.4.
Login
FAQ
Search
