Cross-table mapping

george_ma · **Joined:** Thu Dec 04, 2003 11:59 am **Posts:** 11

I have an object (AutorizedUser), it has an count (numberOfRoles) attribute which used to count the roles this user has and is pointed to another table (Roles).
In my query, I need to grab this user object from User table as well as grab the count of it primary key appeared in the Roles table.
Could any one help me in the mapping and the HQL?
Much thanks.
George

emmanuel · **Posted:** Mon Dec 08, 2003 11:06 am

If you mean count is a column of a different table than the one you used to save AutorizedUser, then you cannot map 1 class into several tables.

But I'm confused about what you want to do.

george_ma · **Joined:** Thu Dec 04, 2003 11:59 am **Posts:** 11

Count is not a column in another table (Roles), it is to count how many roles a particular user has in the Roles table.
I have a Users table and a Roels table. A user may have 0 to many roles, it is an one-to-many mapping. In the application, I don't need to load user with all roles, instead I only want to know how many roles this user has. The idea is to select all user fields from User table with given criteria and select count(*) from Role table where userId=givenUserId.

Thanks

drfish · **Posted:** Mon Dec 08, 2003 3:46 pm

I belive you could use a "formula" or computed property using "select count(*) from table where [where condition]" as the formula

http://www.hibernate.org/hib_docs/refer ... pping-s1-7

(excerpt)

Code:

<property
        name="propertyName"                 
        column="column_name"                
        type="typename"                     
        update="true|false"                 
        insert="true|false"                 
        formula="arbitrary SQL expression"  
        access="field|property|ClassName"   
/>

...

Code:

formula (optional): an SQL expression that defines the value for a computed property. Computed properties do not have a column mapping of their own.

george_ma · **Joined:** Thu Dec 04, 2003 11:59 am **Posts:** 11

Great! I will try now.
Thanks a lot.
George