Simple existence query on many-to-many relationship

cam292 · **Joined:** Fri Oct 01, 2004 7:13 am **Posts:** 20

I have a User class, and an Event class. Users can attend many events. Events have many attendees.

Writing a search in straight sql is simple, but I would like to use the HQL way if I can. Also I am really after the existence rather than a List, but obviously I can work that out in code if there is no Hibernate functionality to achieve this ( I haven't found any yet).

I have an Event instance and a User instance and I would like to see if this User has attended this Event, I'm currently thinking something like this, but I cannot seem to get my head round it - can someone suggest the best way please!

When I have the event, I'm not sure how to access the user, or is that as simple as Event.User.Id ?

session.createQuery("FROM Event event WHERE event.user = :user" ).list();

I think I just need a step up here - very frustrating when you know sql!

Thanks

Hibernate version: 2.1.3

Mapping documents:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="com.company.persistent.User"
table="users"
dynamic-update="false"
>

<id
name="id"
column="id"
type="long"
unsaved-value="any"
>
<generator class="sequence">
</generator>
</id>
<property
name="firstname"
type="java.lang.String"
update="true"
insert="true"
column="firstname"
length="64"
/>

<property
name="lastname"
type="java.lang.String"
update="true"
insert="true"
column="lastname"
length="64"
/>
<key
column="userid"
/>

<set name="attendedEvents" table="attendedEvents" lazy="true">
<key column="userid"/>
<many-to-many class="com.company.persistent.Event" column="eventid"/>
</set>

</class>

</hibernate-mapping>

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="com.company.persistent.Event"
table="events"
dynamic-update="false"
>

<id
name="id"
column="id"
type="long"
unsaved-value="any"
>
<generator class="sequence">
</generator>
</id>
<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="name"
length="255"
not-null="false"
unique="true"
/>
<set name="attendees" table="attendedEvents" lazy="true">
<key column="eventid"/>
<many-to-many class="com.company.persistent.User" column="userid"/>
</set>

</class>

</hibernate-mapping>

cam292 · **Joined:** Fri Oct 01, 2004 7:13 am **Posts:** 20

Please can someone give me some pointers - someone must have used similar search logic.

Thanks

steve · **Posted:** Fri Oct 08, 2004 11:49 am

Code:

SELECT COUNT(DISTINCT event.id)
FROM Event as event 
    INNER JOIN event.attendees as attendee 
WHERE event = :event
    AND attendee = :user

Or you already have the instance of event and the user, why not just do:

Code:

if ( event.getAttendees().contains(user) ) {
    ....
}

cam292 · **Joined:** Fri Oct 01, 2004 7:13 am **Posts:** 20

Thanks Steve.

I would generally resort to the sql, but keep shying away from using HQL as I am so unfamiliar with the syntax, and everything I seem to want to do seems to be tricky apart from the standard attribute search.

The example you gave :

Code:

if ( event.getAttendees().contains(user) ) {

What will the performance of that be like, as I could have 1000 event attendees?

I will try and implement the query you suggested as a sql-query for now.

Thanks for the step up. Always makes so much more sense with a real example on data you are familar with.

Thanks

anthony · **Posted:** Fri Oct 08, 2004 12:46 pm

hql is very easy .... you should consider testing it

steve · **Posted:** Fri Oct 08, 2004 3:09 pm

Don't know why you'd go through all the trouble of setting up the Hibernate mappings and java domain classes, only revert back to vendor-SQL; but its your call I guess...

Quote:

What will the performance of that be like, as I could have 1000 event attendees?

Shouldn't be that bad (why not try it and find out?). Why do you think making network calls to a database server would be faster? Now, if the collection is large, lazy and not yet initialized, the HQL approach would probably be much more performant.

cam292 · **Joined:** Fri Oct 01, 2004 7:13 am **Posts:** 20

Thanks Steve,

I'll give it a go. I think it is just part of getting used to Hibernate. It's just not knowing what Hibernate is doing on the searches. I've had to pull out a couple of queries and implement as sql-query as I was not sure of the HQL and it seemed to be going a little round the houses for more complex queries.

It seems to be great for adding and manipulation as it cleans up a number of things, but examples like this really help. It seems so simple when someone shows it to you, but looking in the book, and reference guide, the examples always seem too simplistic for what you need in the real world!

Thanks again.
I'll feedback any performance findings.