Mapping class to 2 tables problem!

oren · **Joined:** Wed Mar 03, 2004 6:38 am **Posts:** 26

Hi,

I have a class that is mapped to table A, and one of the class properties needs to be retrieved from table B.

Can i define such property in the class map file?

gavin · **Posted:** Fri Apr 02, 2004 5:27 am

Try using a formula property, with a subselect.

oren · **Joined:** Wed Mar 03, 2004 6:38 am **Posts:** 26

gavin wrote:

Try using a formula property, with a subselect.

Sorry, i am not sure i understand what you mean. Could you please explain a bit more?

Thanks

emmanuel · **Posted:** Fri Apr 02, 2004 1:43 pm

a property can be built based on a SQL formula, do do the appropriate SQL to get the B column.
<property name="toto" formula="select b from tableB where.../>

anthony · **Posted:** Fri Apr 02, 2004 2:11 pm

hi emmanuel,
formula= "select b from tableB where..." ok but is it possible in this query to interact with internal field
example formula= "select b from tableB where table B= this.attribut????"

thanks,
Anthony

gavin · **Posted:** Fri Apr 02, 2004 8:36 pm

Code:

<property 
    name = "property" 
    formula = "(select b.column from tableB b where b.id= thisIdColumn)"/>

Of course, the property won't be updateable...

anthony · **Posted:** Sat Apr 03, 2004 3:54 am

very very interesting, i'll try this very soon.

Thanks

oren · **Joined:** Wed Mar 03, 2004 6:38 am **Posts:** 26

emmanuel wrote:

a property can be built based on a SQL formula, do do the appropriate SQL to get the B column.
<property name="toto" formula="select b from tableB where.../>

Thank you ever so much. I will try it.

anthony · **Posted:** Mon Apr 05, 2004 12:03 pm

this feature is working great!!!
how can i "protect" string.
i want my formula to call an oracle fonction, this works perfectly when arguments of this fonction are numbers but when one of the argument is "sysdate", the generated sql become ALIAS.sysdate :(

Thanks,
Anthony

gavin · **Posted:** Mon Apr 05, 2004 12:29 pm

Well, why not provide a patch to Hibernate. Some of the Dialects now have knowledge of the SQL functions available on that platform.

gavin · **Posted:** Mon Apr 05, 2004 12:31 pm

P.S. look at net.sf.hibernate.sql.Template

anthony · **Posted:** Mon Apr 05, 2004 5:15 pm

if you think it is safe to add keyword "sysdate" to Set KEYWORDS of Template.java, i'll provide a patch, i'll also try to find if other "keywords" (supported by most of db) can be added.
The only danger will be to have:
- a db that does not support the keyword
- a column name like the keyword
In fact the most secure would be to have one KEYWORDS Set per dialect or to protect specific db keywords by specials characters ... (for example write "#sysdate#" in place of 'sysdate'

I also notice that if we enter formula="DECODE (....)", it generates ALIAS.DECODE because of the space between DECODE and '(', so it could be helpful to detail it on the doc or in a wiki or in Hibernate in Action, of course it is just a detail... and the source can be slightly modified.

but for the moment i'm spending time on the french translation of the reference doc ... Emmanuel will beat me if i'm too late :) so i add all this on my TODO list ;)

Anthony

emmanuel · **Posted:** Wed Apr 07, 2004 6:38 am

delpouve wrote:

but for the moment i'm spending time on the french translation of the reference doc ... Emmanuel will beat me if i'm too late :) so i add all this on my TODO list ;)

Only if Christian kick my ass too ;-)