We have the objects "Role" and "SystemActionPermission" with a many-to-many relation between them.
We need to retrieve all permissions for the role together with true / false (depending on whether there is a link between specific permission and this role). For this purpose we use the following HQL statement:
Code:
from systemActionPermission as main left join main.role as role with role.id = '4'
This results in the following SQL code:
Code:
select
ap.id as id492_0_,
r.id as id502_1_,
ap.versionlock as versionl2_492_0_,
ap."key" as key3_492_0_,
ap.sort as sort492_0_,
ap.datecreated as datecrea5_492_0_,
ap.dateupdated as dateupda6_492_0_,
ap.usercreated as usercrea7_492_0_,
ap.userupdated as userupda8_492_0_,
ap.active as active492_0_,
ap.moduleidfk as moduleidfk492_0_,
r.versionlock as versionl2_502_1_,
r."key" as key3_502_1_,
r.issuperadmin as issupera4_502_1_,
r.sort as sort502_1_,
r.datecreated as datecrea6_502_1_,
r.dateupdated as dateupda7_502_1_,
r.usercreated as usercrea8_502_1_,
r.userupdated as userupda9_502_1_,
r.active as active502_1_
from
system_actionpermission_orm ap
left outer join
linktable_system_actionpermissionrole_orm ap_role
on ap.id=ap_role.actionpermissionidfk
left outer join
system_role_orm r
on ap_role.roleidfk=r.id
and (
ap_role.roleidfk='4'
)
As it is seen above, there are 2 "left join" clauses (what is correct), but the condition from "with" statement goes to the second one (link between cross-table and role) and not to the first one (link between permission and cross-table). This results in the redundant records that have role ID other than "4".
Any ideas how to change the HQL statement to put the condition inside the first "left join"? Is it a Hibernate bug?
I know that instead of "with" I may use "where", but due to some constraints of my framework I cannot apply this solution.
Thanks
Login
FAQ
Search