combining multi-table search, Criteria and LIMIT x

jme · **Joined:** Mon Jun 06, 2005 8:21 am **Posts:** 13

Hello!

I want to accomplish this rather painful query by my standards (I'd rather see this query a bit simplified):

Code:

SELECT * FROM POI as p WHERE p.city like 'Hel%' and p.name like 'Sok%' AND p.id IN (SELECT po1.id, po2.id FROM POIOptions as po1, POIOptions as po2 WHERE (po1.opt_key LIKE 'email%' AND po1.opt_value LIKE 'jme@%') AND (po2.opt_key LIKE 'url%' AND po2.opt_value LIKE 'www.%') AND p.id IN (SELECT poi.id FROM POI as poi Category as c WHERE poi in elements(c.pois) AND c.id.mainId=? AND c.id.id=?) ORDER BY p.id ASC LIMIT 10); 

(Ignore some small typos, this query is built dynamically)

I intentionally dropped some unnecessary data.

The idea:

Create a search function that searches data from multiple tables.

POIOptions stands for the optional search criterias. The amount of optiopnal data can vary (it's up to the user). So can the Category criteria too.

One POI may have several optional data (POIOptions) and several Categories.

Together (POI, Category) have a many-to-many relation (POI_Category table).

I have it working as far as the 'LIMIT 10' part.

Few days ago I realized (I think) that I cannot use LIMIT x inside a session.find() method (or can I?). I then found the Criteria class which contains setMaxResults(), which sounds nice.

I'm not 100% sure how to do this.

I've tried:

Code:

criteria.add(Expression.sql("{alias}.id in (po.id.id from {alias}.poiOptions as po where po.opt_value LIKE ?)", "www%", Hibernate.STRING) );

which of course didn't work at all.

Any ideas would be nice.

Hibernate version:
2.1.7c
Mapping documents:

Code:

Class Category:

   <class name="Category" table="Category" lazy="true">
   
      <composite-id name="id" class="CategoryId">
         <key-property name="id" type="int" >
            <column name="id" sql-type="INT(10)" />
         </key-property>
         <key-many-to-one name="mainId" class="MainCategory">
            <column name="main_id" sql-type="INT(10)" />
         </key-many-to-one>
      </composite-id>
      
      <property name="name" not-null="true">
         <column name="name" sql-type="VARCHAR(255)" />
      </property>    

      <set name="pois" table="POI_Category" inverse="true" lazy="true"> 
         <key>
            <column name="id" sql-type="INT(10)" />
            <column name="main_id" sql-type="INT(10)" />
         </key>
         <many-to-many class="POI" outer-join="true">
            <column name="poi_id" sql-type="INT(10)" />
         </many-to-many>
      </set>    

   </class>

Class POI:

   <class name="POI" table="POI" batch-size="20" lazy="true">
        <id name="id" type="int">
            <column name="id" sql-type="int(10)" not-null="true" />
            <generator class="native"/>
        </id>
</class>

Class POIOptions:

   <class name="POIOptions" table="POIOptions" lazy="true">
      <composite-id name="id" class="fi.tietotalo.dmm.domain.Id" >
         <key-property name="id">
            <column name="id" sql-type="INT(10)" />
         </key-property>
         <key-property name="opt_key">
            <column name="opt_key" sql-type="varchar(255)" />
         </key-property>
      </composite-id>   
      <property name="opt_value"> 
         <column name="opt_value" sql-type="text" />
      </property>
   </class>

Name and version of the database you are using:
MySql 4.1.12 Win

sven · **Posted:** Wed Aug 17, 2005 12:09 pm

You can use LIMIT neither in HQL, nor in Criteria API.

As you said you have to use setMaxResults.

Example:

Code:

Criteria crit = session.createCriteria(Cat.class);
crit.add( Expression.eq( "color", eg.Color.BLACK ) );
crit.setMaxResults(10);
List cats = crit.list();

See the docs for more information.
http://www.hibernate.org/hib_docs/refer ... rycriteria

Best regards
Sven