Hi,
Does anyone know how to restrict the number of columns selected by the elements()" function? I can't seem to figure it out, but I got an impression that Postgres only accepts a single column selects for ANY clauses.
Here is an example. The query below should get all rooms where the list of room codes contain codes with 'BK' and 'DE'.
Code:
from EnumerationFieldValue as instanceValues
where instanceValues.field = 'room_code'
and 'BK' = any elements(instanceValues.values)
and 'DE' = any elements(instanceValues.values)
It translates to the following SQL query:
Code:
select
enumeratio0_.field_value_id as field1_158_,
enumeratio0_1_.field as field158_,
enumeratio0_1_.listing_id as listing3_158_
from
EnumerationFieldValue enumeratio0_
inner join
FieldValue enumeratio0_1_
on enumeratio0_.field_value_id=enumeratio0_1_.field_value_id
where
enumeratio0_1_.field='room_code'
and 'BK'=any (
select
values1_.name,
values1_.shortName,
values1_.code,
values1_.parent
from
EnumerationFieldValue_values values1_
where
enumeratio0_.field_value_id=values1_.EnumerationFieldValue_field_value_id
)
and 'DE'=any (
select
values2_.name,
values2_.shortName,
values2_.code,
values2_.parent
from
EnumerationFieldValue_values values2_
where
enumeratio0_.field_value_id=values2_.EnumerationFieldValue_field_value_id
)
and 'SU'=any (
select
values3_.name,
values3_.shortName,
values3_.code,
values3_.parent
from
EnumerationFieldValue_values values3_
where
enumeratio0_.field_value_id=values3_.EnumerationFieldValue_field_value_id
)
Which gives
Code:
ERROR: subquery has too many columns
The modified query from above works like charm:
Code:
select
enumeratio0_.field_value_id as field1_158_,
enumeratio0_1_.field as field158_,
enumeratio0_1_.listing_id as listing3_158_
from
EnumerationFieldValue enumeratio0_
inner join
FieldValue enumeratio0_1_
on enumeratio0_.field_value_id=enumeratio0_1_.field_value_id
where
enumeratio0_1_.field='room_code'
and 'BK'=any (
select
values1_.code
from
EnumerationFieldValue_values values1_
where
enumeratio0_.field_value_id=values1_.EnumerationFieldValue_field_value_id
)
and 'DE'=any (
select
values2_.code
from
EnumerationFieldValue_values values2_
where
enumeratio0_.field_value_id=values2_.EnumerationFieldValue_field_value_id
)
and 'SU'=any (
select
values3_.code
from
EnumerationFieldValue_values values3_
where
enumeratio0_.field_value_id=values3_.EnumerationFieldValue_field_value_id
)
Can anyone please point out what I am doing wrong with this one? Thank you very much in advance.
Best regards,
Nick.
Hibernate version: 3.2.4
Mapping documents:Code:
@Entity
public class EnumerationFieldValue {
@CollectionOfElements
@Embedded
private List<EnumerationOption> values =
new ArrayList<EnumerationOption>();
...
}
@Embeddable
public class EnumerationOption implements Serializable {
private static final long serialVersionUID = 1L;
private String name;
private String shortName;
private String code;
@Type(type="persistence.FieldUserType")
private EnumerationField parent;
...
Code between sessionFactory.openSession() and session.close(): N/A
Full stack trace of any exception that occurs: N/A
Name and version of the database you are using: PostgreSQL 8.2
The generated SQL (show_sql=true): See above
Debug level Hibernate log excerpt: N/A
Login
FAQ
Search