HQL problem - Checking for empty collections

[Einar]

I'm trying to write an HQL query that needs to check for an empty
collection.

My query looks something like this:

Select a from Announcement a
inner fetch join a.Groups groups
join groups.UserInGroup uig
where
(
uig.Role = some elements(a.Group_Roles)
or a.Group_Roles is null <-- the problem is here
) and uig.User.id = XX

Do give you a little info about what my domain looks like. I have
Announcements which can be "published" to groups. This is done using a
many-to-many mapping (via m2m table) to the Groups table. This m2m
mapping table also contains a field to restrict what role in the group
the announcement should be visiable to.
So my table are the following:
Announcements,
Announcment_Publications, <-- A many-to-many table that defines what
groups/group roles should see the announcement
Groups,
UsersInGroup
Users

In my query I want to check that the user's role in the group (defined
in the UsersInGroup table) matches the role defined in the m2m table
OR that not role is defined in the m2m table, just a group (this would
be a NULL value in the role field).

I've mapped the roles from the m2m table to the Announcement class
using the following mapping:
<bag name="Group_Roles" table="Announcement_Publications">
<key column="Anp_Announcement_id"/>
<element column="Anp_Group_Role" type="string"/>
</bag>
The groups are mapped like this:
<bag name="Groups" lazy="false" inverse="true" cascade="all"
table="Announcement_Publications">
<key column="Anp_Announcement_id" />
<many-to-many fetch="join" class="N.Group, N"
column="Anp_Group_id" />
</bag>

When I try something like the above query to check if the roles field
in the m2m table is null, I get this exception:
NHibernate.QueryException: unindexed collection before []

I hope I've provided enough information about my domain and mapping
for you to understand my setup.

How could I write the above query to check for "null" roles? Is my
mapping correct?

[Ayende Rahien]

I think that there is the emtpy( ) function, but I am not sure...

Select a from Announcement a
inner fetch join a.Groups groups
join groups.UserInGroup uig

join a.Group_Roles gr
where (uig.Role = some elements(a.Group_Roles) or a.Group_Roles is empty)
 and uig.User.id = XX


Anyway, you can use:

Select a from Announcement a
inner fetch join a.Groups groups
join groups.UserInGroup uig

join a.Group_Roles gr
where (uig.Role = some elements(a.Group_Roles) or gr is null ) and uig.User.id = XX

[Fabio Maulo]

Select a from Announcement a
inner fetch join a.Groups groups
join groups.UserInGroup uig
where
(
uig.Role = some elements(a.Group_Roles)

or size(a.Group_Roles) = 0

) and uig.User.id = XX

Bye.
Fabio Maulo

[Einar]

Thanks for the help guys.

Ayende's first solution results in the same exception as using my
initial attempt. His second solution returns the right data but
results in the enties of the Groups collection of an Announcement item
to be duplicated (actually every group is repeated for ecery
associated group resulting in N^N groups being associated with each
announcement), this results in a huge and unnecessary amout of data
being transfered from the DB. This is probably due to the extra join
statement "join a.Group_Roles gr"

Fabio's solution does not work since, for some strange reason, the
size() method does not return null. I only get announcements from
groups where the role restriction matches my role and not the other
announcements for my groups with no restrictions. Is there any way to
check the value of the size() method?

Do you have any other ideas?

[Fabio Maulo]

2008/3/31, Einar <ein...@gmail.com>:

Fabio's solution does not work since, for some strange reason, the
size() method does not return null. I only get announcements from
groups where the role restriction matches my role and not the other
announcements for my groups with no restrictions. Is there any way to
check the value of the size() method?

Do you have any other ideas?

 

I don't understand. The size() method return a Int32.
My example mimic your first HQL.
Sometimes you are looking for something else:

Select a from Announcement a
inner fetch join a.Groups groups
join groups.UserInGroup uig
where

(uig.Role in elements(a.Group_Roles)) and uig.User.id = XX

Bye.
Fabio Maulo

[Einar]

Thank you for your reply Fabio

What is it you don't understand? The problem with using the size()
method is that it doesn't seem to return 0 when the collection is
empty. At least it doesn't make any difference in the result (and it
should do so according to the data in the DB) whether or not I include
the "or size(a.Group_Roles)=0".

If I try a more simple case where I just want to find all
announcements which are published to some group (the inner join fetch
criteria) and where the role is not set (the size() = 0 criteria), I
would write the following HQL query:

Select a from Announcement a
inner fetch join a.Groups groups

where
size(a.Group_Roles)=0

This translates in to the following SQL query

select *
from Announcments announceme0_
inner join Announcement_Publications groups1_ on
announceme0_.announcement_id=groups1_.Anp_Announcement_id
inner join Groups group2_ on groups1_.Anp_Group_id=group2_.Group_ID
where ((select count(*) from Announcement_Publications group_role3_
where
announceme0_.announcement_id=group_role3_.Anp_Announcement_id)=0 )


Clearly the subselect query generated for the size() function does not
work as a criteria for checking the Anp_Group_Role field for null,
since the field isn't event mentioned in the query. The subselect
should have looked something like this right?:
(select count(*) from Announcement_Publications group_role3_ where
announceme0_.announcement_id=group_role3_.Anp_Announcement_id and
group_role3_.Anp_Group_id=groups1_.Anp_Group_id and
group_role3_.Anp_Group_Role is null)

I hope you can follow what I'm trying to achive here.

> (uig.Role in elements(a.Group_Roles)) and uig.User.id <http://uig.user.id/>= XX
>
> Bye.
> Fabio Maulo