HQL- qiery intersections on set associations?

sekullbe · **Joined:** Wed Mar 17, 2004 6:47 pm **Posts:** 1

I'm using Hibernate 2.1 on Postgres 7.3.2.

I've got an object with a fairly simple set property- a "Task" (my object) has any number of "AssignmentGroups" (strings) associated with it.

What I'm trying to do is, given a Collection of AssignmentGroup names, get all the Tasks that match any of them. I first tried this:
"from Task t where some elements (t.groups) in (:myGroups)"

but if there was more than one Task with the same group, it breaks: "More than one tuple returned by a subselect used as an expression." Makes sense in hindsight.

I can do it by inserting a limit clause into the raw SQL that query creates:
"select distinct t.code from task as t where ((select tag.group_name from task_assignment_groups tag where t.code=tag.task_code limit 1) in ('grp1', 'grp2')"
but I can't seem to get that to work with createSQLQuery- whether I don't understand the alias system or it just can't be done I don't know.

Can I do this with HQL? Do I need to fix my SQLQuery (probably generating another help-seeking post)? Or should I just grab the DB connection and do it in JDBC?

Here's the relevant part of my mapping file for 'Task'- it was generated by xdoclet which I'll also throw in there. 'code' is the primary key for the Task table.

Code:

 
   * @hibernate.set lazy="true" table="task_assignment_groups"
   * @hibernate.collection-key column="task_code"
   * @hibernate.collection-element column="group_name" type="string"

       <set
            name="assignmentGroups"
            table="task_assignment_groups"
            lazy="true"
            inverse="false"
            cascade="none"
            sort="unsorted">                                                                              
              <key
                  column="task_code"
              />                                                                      
              <element
                  column="group_name"
                  type="string"
                  not-null="false"
                  unique="false"/>
 </set>

and the code:

Code:

String queryStr = "select distinct task.code from HibernateTaskData as task where "
+"elements (task.assignmentGroups) in ("+quoteAndCommify(groups)+") ";
List groupTaskCodes = HibernateTaskData.query(queryStr);

quoteAndCommify just breaks lists into 'words', 'like','this'. For fairly ugly legacy reasons I've got to just build the query as a String in one pass.

I'll add any more info as requested since this more of a 'how do I do this' than 'why is this breaking' issue.

Thanks very much!
-Scott

emmanuel · **Posted:** Fri Mar 19, 2004 6:24 am

I've read fast but does that help http://forum.hibernate.org/viewtopic.php?t=929068