0

Can I use HQL to get the index of an ordered query result?

The only way I know to do this is to get back all of the results from the database, then iterate through all of the results.

For example, given:

<hibernate-mapping>
<class name="Dog" table="DOG">

    <id name="id" type="int" column="DOG_id">
        <meta attribute="scope-set">protected</meta>
        <generator class="native"/>
    </id>

    <property name="age" type="int" />

    <property name="name" type="string" />
</class>    
</hibernate-mapping>

then these types of queries:

//determine where dog with id 43 is in the current search results for dogs ordered by age
Query dogQuery = sess.createQuery("select d.id from Dog as d order by d.age");
List<Dog> dogList = dogQuery.list();
int idx = dogList.indexOf( 43 );

or, a more refined search through the dog list:

Query dogQuery = sess.createQuery("select d.id from Dog as d where (d.name='fluffy' OR d.age >= 3) order by d.age");
List<Dog> dogList = dogQuery.list();
int idx = dogList.indexOf( 43 );

The drawback here is that we are loading every dog.id into memory.

Scenario where I would need this:

  • displaying a specific query result (of thousands) as a dot on a line. The line, and the dot, gets updated every minute or so. This visualization gives "real time" updates on the rank of a search query
jedierikb
  • 12,752
  • 22
  • 95
  • 166

5 Answers5

1

use the List.indexOf() If the list is extra large then make the list "extra-lazy".

But why do you need the index? Maybe you really don't (and thus you will not need to solve this issue)

Revised answer:

You are doing it completely wrong. All you need is :

select count(*) from dog d, dog d1 where d.age <= d1.age and d1.id = :entityId and d1.id <> d.id

Why this works: This query finds every dog that is the same age or younger than the dog in question (d1) but is not d1. (but does not actual return all those dogs - since we don't care about them).

The count tells you the number of dogs "before" d1. Thus you can tell where d1 appears in the complete list of dogs. Since you don't need to know the position of all the dogs in the list, you don't need to retrieve all the dogs.

Pat
  • 5,761
  • 5
  • 34
  • 50
  • One scenario where I would need this: if I need to return to the correct page of results for a product, and the catalog is always changing. – jedierikb Feb 13 '09 at 17:40
  • Even if the list is extra-lazy, I still won't know which index is the one I am looking for unless I check each item (defeating the extra-laziness). (if i understand extra-lazy correctly). – jedierikb Feb 13 '09 at 17:43
  • "extra-lazy" means only elements of the list are fetched as needed. http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#performance-fetching-lazy so List.indexOf() should work because the backing PersistantList should just generate the query to return the result. – Pat Feb 13 '09 at 18:58
  • But looking at your question again, List is the wrong collection to use. Hibernate stores an int 0-to-whatever as part of each element. If the items in the list are constantly changing order, thats a lot of updating. I would suggest bag or maybe sorted set. – Pat Feb 13 '09 at 19:02
  • We persist a set. The query returns a list from that set. I am looking for an item index in that list. (in the midst of trying extra-lazy, btw). – jedierikb Feb 13 '09 at 19:12
  • Can't seem to specify extra-lazy for an hql query such as: "select d.id from Dog as d where.." Only settable in the hbm.xml of a collection? – jedierikb Feb 13 '09 at 19:52
  • An object can have 2 different views on to the same collectionone that is extra-lazy. HOWEVER, this really smells like the wrong approach. Think you need to step back and ask if you are using the correct hibernate constructs. Please revise your question with more details. – Pat Feb 13 '09 at 20:15
  • Question: hitting db is expensive. Is it possible to make the experience less "real-time"? How about aggregating some data? Feel like you are down in the weeds and not really understanding the desired result. – Pat Feb 13 '09 at 20:18
  • Pat: I updated the question with some simple code to help illustrate the basics of the problem. Thanks for your help with this. – jedierikb Feb 13 '09 at 21:12
  • Very interesting; although not sure i completely understand your thinking. Does this solution work if I was sorting on arbitrary where clauses (see 2nd example added to OP)? – jedierikb Feb 13 '09 at 22:11
  • Sure it does. You are just need to create a query that has a where clause that returns all the items "before" the item in question. – Pat Feb 14 '09 at 07:29
  • If I understand your answer, 'd.age <= d1.age' is the key here to finding the count "up to" the entityId. I am sorry to belabor, but does this would work if 'order by' is alphabetical? Of if i had multiple order-by criteria? – jedierikb Feb 15 '09 at 04:35
  • I think the best thing for me to say at this point is.... try it and get the answer yourself. – Pat Feb 15 '09 at 11:47
  • Thank you, it works. I am not sure why though.. the 'd.age <= d1.age' bit throws me off. Very glad to have had your help. – jedierikb Feb 16 '09 at 05:21
  • Pat: whenever you get a chance, I'd really like to better understand how this works. So glad that it does, but understanding will keep me from posting more questions (and answering more for others). – jedierikb Feb 26 '09 at 16:51
0

In a java List, you can do a call to indexOf(Object).

See List for the API.

fospathi
  • 537
  • 1
  • 6
  • 7
tehvan
  • 10,189
  • 5
  • 27
  • 31
  • Thank you, but I am hoping to avoid loading the whole list into memory from the database... let me clarify my question. – jedierikb Feb 09 '09 at 14:45
0

Loading the whole list of results into memory and then using application logic just to filter what you need is the WRONG way to interact with a database.

Use the SQL WHERE and HAVING clauses to get what you need from the DB in the first place.

kazanaki
  • 7,988
  • 8
  • 52
  • 79
0

starting with this HQL (valid SQL too, in this case):

select dog.id as dogId from canines dog order by dog.codename asc

It can be slightly modified and inserted into a valid SQL query (only for Oracle with the rownum column):

select rn from 
( select dogid dogId, rownum rn from canines dog order by dogname asc)
where dogid=206

Unfortunately, this is not valid HQL...

To use my initial HQL query to get the same information as that SQL query, I plan to:

  • have hibernate generate my HQL query into the SQL it would use to make the list of all results (hopefully not running the query first)
  • insert this hibernate-generated SQL into a new SQL query using sessionObj.createSQLQuery. This new query will look like the working sql above.

Is this a crazy solution?

--

notes

SQL to return the rownum of a specific row? (using Oracle db)

Community
  • 1
  • 1
jedierikb
  • 12,752
  • 22
  • 95
  • 166
0

You should be able to use something like Hibernates Criteria object to return just a small subsection of your query.

My guess is you're trying to implement pagination.

Criteria criteria=session.createCriteria(Item.class);
criteria.addOrder(Order.asc("name"));
criteria.setFirstResult(100);
criteria.setMaxResults(50);
List pageResults=criteria.list();

The above code was lifted from: http://forum.springframework.org/archive/index.php/t-9658.html

This will also work for a catalog that is always updated since you should be making this query on every page request.

hooknc
  • 4,854
  • 5
  • 31
  • 60
  • I want to know *which* page of my results has an item I am looking for so that I can use setFirstResult correctly. – jedierikb Feb 13 '09 at 17:56
  • If you know the item you're looking for then just display that item. Then show a link to the catalog the item came from. Of course I don't know all your requirements, but trying to go to a specific page of a catalog to display an item you know the id for is a little strange. – hooknc Feb 13 '09 at 18:03
  • Thanks, hooknc. Here's another use case (without pagination): displaying a specific query result (of thousands) as a dot on a line. The line, and the dot, gets updated every minute or so. This visualization gives "real time" updates on the rank of a search query. – jedierikb Feb 13 '09 at 18:22