I have a use case where there are

categories

subCategories

articles

Customer wants to filter via user attributes at each of these levels and if no data exists at the sub level then don't show the parent.

i.e If there are no articles for a subCategory then don't show subCategory and if that evaluates that now there are no subCategories for a category then don't show category.

1) Is there any HQL construct or other mechanism that would allow for this type of behaviour irrespective of the permissioning issue and just focusing on parent child relationships? h2 or h3?

2) To implement permissioning what is the recommended practice. Is this currently possible with just mapping files and filtering ( I couldn't see this )

Basically my filtering at the db level would be implenented by many-to-many with the level -> permission

I'm looking for a way to enable filters and all I would need to retreive is the lead level in the mapping file (the empty lead levels would be removed automatically by the mechanism mentioned in 1) and base on rows returned after implementing filter.

I'm looking for some insite on recommended ways to proceed to address this with hibernate

AFAICT, this is a straighforward case for HB3 filters.

Below is a typical usecase. The SecureHomeItemRel is the permission table. The customer wants to protect SecureHomeItemBulletsVO by the same set of permissions. typeSpecialtyId,roleId,networkPlanId. The current mapping is all messed up.


What I really would like to do is return SecureHomeContentVO to jsp and have the mappings take care of all the other queries. The problem is I don't see where to add filters since unless I'm incorrect the filtering would have to happen with query loaders and the permissioning query is like

select x from table where exists (from Rel table where permission row exists)

I can't see where I could get the filter to apply to the nested exists select.

Please correct my assumptions and show me if there is a way to accomplish this.

--------------------------------------

<class name="com.bcbsma.providerportal.navigation.model.SecureHomeItemRelPKVO" table="tbl_secure_home_items_rel">
<composite-id name="primaryKey" class="com.bcbsma.providerportal.navigation.model.SecureHomeItemRelVO">
<key-property
name="contentId"
type="java.lang.Long"
column="SECURE_HOME_CONTENT_ID"
/>
<key-property
name="itemId"
type="java.lang.Long"
column="SECURE_HOME_ITEM_ID"
/>
<key-property
name="typeSpecialityId"
column="PROVIDER_TYPE_SPECIALTY_ID"
type="java.lang.String"
/>
<key-property
name="roleId"
column="BCBSMA_ROLE_ID"
type="java.lang.Long"
/>
<key-property
name="networkPlanId"
column="NETWORK_PLAN_ID"
type="java.lang.Long"
/>
</composite-id>
</class>


<class name="com.bcbsma.providerportal.navigation.model.SecureHomeContentVO" table="tbl_bcbsma_secure_home_content">
<!-- A 32 hex character is our surrogate key. It's automatically

generated by Hibernate with the UUID pattern. -->
<id name="contentId" type="java.lang.Long" unsaved-value="null" >
<column name="SECURE_HOME_CONTENT_ID" sql-type="number" not-null="true"/>
<generator class="assigned"/>
</id>
<property name="imageFileName" type="java.lang.String">
<column name="SECURE_HOME_IMG_FILENAME" sql-type="varchar(200)" not-null="false"/>
</property>
<property name="internalURLId" type="java.math.BigDecimal">
<column name="INTERNAL_URL_ID" sql-type="number" not-null="true"/>
</property>
<property name="imageAltText" type="java.lang.String">
<column name="SECURE_HOME_IMG_ALTTEXT" sql-type="varchar(4000)" not-null="false"/>
</property>

<property name="introText" type="java.sql.Blob">
<column name="SECURE_HOME_INTRO_TEXT" sql-type="blob" not-null="false"/>
</property>

<property name="createdDate" type="java.sql.Date">
<column name="CONTENT_CREATED_DT" sql-type="date" not-null="false"/>
</property>

</class>

<class name="com.bcbsma.providerportal.navigation.model.SecureHomeContentItemVO" table="tbl_secure_home_items">
<!-- A 32 hex character is our surrogate key. It's automatically
generated by Hibernate with the UUID pattern. -->
<id name="itemId" type="java.lang.Long" unsaved-value="null" >
<column name="SECURE_HOME_ITEM_ID" sql-type="number" not-null="true"/>
<generator class="assigned"/>
</id>
<property name="internalURLId" type="java.math.BigDecimal">
<column name="INTERNAL_URL_ID" sql-type="number" not-null="true"/>
</property>
<property name="contentId" type="java.lang.Long">
<column name="SECURE_HOME_CONTENT_ID" sql-type="number" not-null="false"/>
</property>

<property name="itemText" type="java.lang.String">
<column name="SECURE_HOME_ITEM_TEXT " sql-type="varchar(200)" not-null="false"/>
</property>

<property name="itemContent" type="java.sql.Blob">
<column name="SECURE_HOME_ITEM_CONTENT" sql-type="blob" not-null="false"/>
</property>

<property name="pinActivated" type="java.lang.String">
<column name="PIN_ACTIVATED " sql-type="varchar(1)" not-null="true"/>
</property>

<property name="accessTypeId" type="java.lang.String">
<column name="BCBSMA_ACCESS_TYPE_ID " sql-type="varchar(20)" not-null="true"/>
</property>

</class>


<class name="com.bcbsma.providerportal.navigation.model.SecureHomeItemBulletsVO" table="tbl_secure_home_item_bullets">
<!-- A 32 hex character is our surrogate key. It's automatically
generated by Hibernate with the UUID pattern. -->

<id name="bulletId" type="java.lang.Long" unsaved-value="null" >
<column name="SECURE_HOME_ITEM_BULLET_ID" sql-type="number" not-null="true"/>
<generator class="assigned"/>
</id>

<property name="contentId">
<column name="SECURE_HOME_CONTENT_ID" sql-type="number" not-null="false"/>
</property>

<property name="itemId">
<column name="SECURE_HOME_ITEM_ID" sql-type="number" not-null="false"/>
</property>

<property name="bulletName">
<column name="ITEM_BULLET_NAME " sql-type="varchar(200)" not-null="false"/>
</property>
<property name="bulletURL">
<column name="ITEM_BULLET_URL" sql-type="varchar(200)" not-null="false"/>
</property>
<property name="internalURLId" type="java.math.BigDecimal">
<column name="INTERNAL_URL_ID" sql-type="number" not-null="false"/>
</property>
</class>



</hibernate-mapping>

<set name="secureContentItems"
inverse="true"
lazy="false"
cascade="all"
>
<key column="SECURE_HOME_CONTENT_ID"/>
<one-to-many class="com.bcbsma.providerportal.navigation.model.SecureHomeContentItemVO"/>
<filter name="secure" condition="exists (
SELECT 'x' from tbl_secure_home_items_rel rel
WHERE
rel.PROVIDER_TYPE_SPECIALTY_ID IN (:specialtyType,:allTypeSpecialty)
AND rel.NETWORK_PLAN_ID IN (:networkPlanIds,:allNetworkPlans)
AND rel.BCBSMA_ROLE_ID IN (:roleIds,:allRoles)
AND rel.SECURE_HOME_CONTENT_ID = [collectionalias].SECURE_HOME_CONTENT_ID
AND rel.SECURE_HOME_ITEM_ID = [collectionalias].SECURE_HOME_ITEM_ID
)"/>

</set>
but I can't figure out how specify collection alias.

May be it will be simpler to load ALL articles at once and get subcategories from them?


In the end you will get only needed objects and can build tree from them.

Turns out all unqualified columns assume the alias of root query.