Hibernate Community • View topic - HQL - How would you search for a subset of a collection?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

HQL - How would you search for a subset of a collection?

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

Kintar

Post subject: HQL - How would you search for a subset of a collection?

Posted: Thu May 19, 2005 11:23 am

Newbie

Joined: Tue Apr 19, 2005 3:16 pm
Posts: 18

Hibernate version: 3

Hello, everyone. I've got a generic, but not necessarily simple, HQL question.

I have a database of bibliography citations. Each citation has a list of associated keywords. Keyword and Citation are both mapped objects, with a many-to-many relationship. Each object maintains a collection of references. I.e., Citation has a list of Kewords, and Keyword has a list of Citations.

One of the requirements of the search functionality for this app is to be able to search for citations by keyword, but I'm having some trouble figuring out how to write an HQL query for a citation that contains one or more of a list of keywords. In native SQL, I would be pulling the list of citation ids from the citation-keyword join table something like this:

Code:

SELECT citationID
FROM   citation_keywords
WHERE  keywordID IN ( <keyword ID list goes here> );

I'm still trying to get my head completely around HQL, though. I know I can reference the keywords collection on the Citation object like this:

Code:

from Citations cite
where 1450 IN elements(cite.keywords.id)

But what do I do since I'm looking for one or more keywords in that collection?

Thanks in advance for all your help!

-- Kintar

Top

Kintar

Post subject: Re: HQL - How would you search for a subset of a collection?

Posted: Thu May 19, 2005 11:25 am

Newbie

Joined: Tue Apr 19, 2005 3:16 pm
Posts: 18

Bah. Forgot to include the mapping documents. The relevant portions are here:

Code:

   <class name="net.infinities.biblio.Keyword" table="keywords">
      <id name="id" type="long" unsaved-value="0">
         <column name="id" sql-type="int(16)" not-null="true"/>
         <generator class="org.hibernate.id.IdentityGenerator"/>
      </id>
      
      <property name="word" type="string">
         <column name="word" sql-type="varchar(255)" not-null="true" unique="true"/>
      </property>
      
      <property name="comment" type="string">
         <column name="comment" sql-type="varchar(255)"/>
      </property>
      
      <set name="citations" table="citation_keywords" cascade="all">
         <key>
            <column name="keyword_id" sql-type="int(16)"/>
         </key>
         <many-to-many class="net.infinities.biblio.Citation">
            <column name="citation_id" sql-type="int(16)"/>
         </many-to-many>
      </set>
      
   </class>

   <class name="net.infinities.biblio.Citation" table="citations">
      <id name="id" type="long" unsaved-value="0">
         <column name="id" sql-type="int(16)" not-null="true"/>
         <generator class="org.hibernate.id.IdentityGenerator"/>
      </id>
      
      <property name="citation" not-null="true" unique="true">
         <column name="citation" sql-type="text"/>
      </property>
      
      <property name="summary">
         <column name="summary" sql-type="text"/>
      </property>
      
      <property name="year" type="string">
         <column name="year" sql-type="varchar(32)"/>
      </property>
      
      <set name="keywords" table="citation_keywords" cascade="all">
         <key>
            <column name="citation_id" sql-type="int(16)"/>
         </key>
         <many-to-many class="net.infinities.biblio.Keyword">
            <column name="keyword_id" sql-type="int(16)"/>
         </many-to-many>
      </set>

Top

Kintar

Post subject: Well THAT was idiodically simple. :)

Posted: Thu May 19, 2005 12:17 pm

Newbie

Joined: Tue Apr 19, 2005 3:16 pm
Posts: 18

Code:

select c from Citation where c.keywords.id in ( 1, 5, 14 )

*sigh* I need to just learn to let the tool do it's job, eh? :)

-- Kintar

Top

Nebob

Post subject:

Posted: Thu May 19, 2005 2:40 pm

Senior

Joined: Thu May 12, 2005 11:40 pm
Posts: 125
Location: Canada

You might like to use the HQL elements() function. It seems suited to what you're trying to do.

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum