Hi All,

I've just registered to the hibernate forum.
I'm developing a web application. I'd like to load the menus from a database, but always only the menus belonging to an appropriate role.

So I have 3 tables:
user_roles(id, descr)
menus (id, descr, link, pos, type, parent_id)
menu_rights(menu_id, role_id)

I created the appropriate base xml files. (After the tutorial, so nothing complex,.)
(For example in the UserRole.hbm.xml:
...
<set name="menus" table="menu_rights" >
<key column="role_id"/>
<many-to-many column="menu_id" class="Menu"/>
</set>
.....

in the Menu.hbm.xml:
...
<set name="children" inverse="true" lazy="true">
<key column="parent_id"/>
<one-to-many class="Menu"/>
</set>
......

)

My problems are:
- When calling UserRole.getMenus() I get all the menus belonging to the role, but I'd like to get only the top-level menus:
- After getting the top-level menus, and then calling menu.getChildren() I get all the direct sub-menus, but I would like to get only the ones that belong to the role with that I got the top-level menus. So I would like always to see the menus that belong to an appropriate role.
Can I do it with the hibernate?

Thnx
George

Have you tried formula?

I haven't tested this,

<set name="menus" table="menu_rights" >
<key column="role_id"/>
<many-to-many formula="select menu_id from menu_rights, menus where menu_rights.id=menu_id and parent_id is null" class="Menu"/>
</set>

assuming that the parent id is null for top level menus.

look at h3 filters

Hi!

It's very strange. I'm getting ClassCastException with this, but when I use column instead of formula it works (at least I got all the menus:)).

George

Ok I will look at it. But in the Menu class I don't store role_id, I think that is the problem. From a menu I'm always getting all the children and not only the children with the appropriate roles.
It would be good to get the whole Menustructure when calling UserRole.getMenus(). I mean the whole tree, and then when I call menu.getChildren() then no sql will be executed but the previously results would be used. So without hibernate I would do it in that way I think: building the menustructure(tree) in advance from the role. Maybe it is possible also with hibernate?

Or Should I create the menu class from the menu_rights and menu tables (Maybe join in the hibernate-mapping?) so that I always have the role_id in the Menu class?

I tried a where=.. condition in the set instead of formula. the where condition worked.

<set name="menus" table="menu_rights" where="parent_id is null">
<key column="role_id"/>
<many-to-many column="menu_id" class="Menu"/>
</set>