Hi there
I got two tables in my Database model :
T_RESTAURANT and T_RATING
I have written the following mapping for the Restaurant Class
Quote:
...
<property name="score" generated="always" update="false" insert="false">
<formula>
(select avg(r.RATING) from T_RATING r where r.RESTAURANT_ID = RESTAURANT_ID)
</formula>
</property>
...
It generates the following SQL code :
Quote:
select
restaurant0_.ID_RESTAURANT as ID1_1_0_,
restaurant0_.NAME as NAME1_0_,
restaurant0_.DESCCRIPTION as DESCCRIP3_1_0_,
restaurant0_.STREET as STREET1_0_,
restaurant0_.CITY as CITY1_0_,
restaurant0_.PROVINCE as PROVINCE1_0_,
restaurant0_.ZIPCODE as ZIPCODE1_0_,
restaurant0_.COUNTRY as COUNTRY1_0_,
(select
avg(r.RATING)
from
T_RATING r
where
r.RESTAURANT_ID = restaurant0_.RESTAURANT_ID) as formula0_0_
from
eatproject_db.T_RESTAURANT restaurant0_
where
restaurant0_.ID_RESTAURANT=?
and create the following error :
Quote:
Unknown column 'restaurant0_.RESTAURANT_ID' in 'where clause'
a correct request for MySQL would be :
Quote:
select
restaurant0_.ID_RESTAURANT as ID1_1_0_,
restaurant0_.NAME as NAME1_0_,
restaurant0_.DESCCRIPTION as DESCCRIP3_1_0_,
restaurant0_.STREET as STREET1_0_,
restaurant0_.CITY as CITY1_0_,
restaurant0_.PROVINCE as PROVINCE1_0_,
restaurant0_.ZIPCODE as ZIPCODE1_0_,
restaurant0_.COUNTRY as COUNTRY1_0_,
(select
avg(r.RATING)
from
T_RATING r
where
r.RESTAURANT_ID = <ID_VALUE>) as formula0_0_
from
eatproject_db.T_RESTAURANT restaurant0_
where
restaurant0_.ID_RESTAURANT=<ID_VALUE>
IS there a workaround to use that kind of function with MySQL
Thinks for your help
Login
FAQ
Search