0

I have this query:

em.createQuery("select new SomeDto(some.name, " +
"(select max(other.weight) from Other other where other.someId = some.id) as otherWeight" +
") from Some some order by otherWeight")
.getResultList();

Which does not work because hibernate ignores the as otherWeight and simply generates as col_1_0_ instead.

Is there any way with this kind of select new dto query to get aliases for columns? If not: how can i do such a query (map result of query to DTO constructor)?

NoUsername
  • 693
  • 6
  • 20

1 Answers1

0

You can try the following query, which should also be faster:

select new SomeDto(some.name, max(other.weight))
from Other other right outer join other.some some
group by some.id, some.name
order by max(other.weight)

You need to introduce the many-to-one (or one-to-one) association from Other to Some if you don't have one already.

Dragan Bozanovic
  • 23,102
  • 5
  • 43
  • 110
  • this works if there is always an `Other` for each `Some` but in case this is optional I would exclude those results (inner join) right? – NoUsername Dec 18 '15 at 08:51
  • You're probably right. My initial goal was to somehow use an alias in the "order by" clause to make the whole thing more readable (imagine that instead of `max(other.weight)` there is some more complex expression) however, it seems that this is something one would need more in "pure sql" if you write queries by hand a lot. When constructing such jpql queries such expressions could be put into variables and concatenated that way to still keep it readable. So I think there isn't really a solution for what I wanted because it isn't something one "should want". – NoUsername Jan 04 '16 at 12:39