1

We currently have a problem displaying ordered objects with subqueries.

We have a table where we save the order of objects corresponding to their ranking. Now, if someone wants to see the actual objects themselves, we make the following query (query simplified and names changed):

from Object as object where object.id = some ( select objectid from RankingObject where type = :object_type ))

As we dont want to rank all the objects each time such a query is requested, we execute the ranking (and the creation of the RankingObjects) periodically and then just find the objects corresponding to the RankingObjects, which are ordered based on the ranking algorithm (highest ranking object is the first one in the table).

The subquery returns the objects in the correct order, just as they are saved in the database. The outer query returns the correct objects, but the order of them does not correspond to the inner query, but to the order of occurrence in the database (oldest objects appear first).

My question: How can we enforce the order of the inner query on the outer one? Of course, doing one query for each of the subquery items is no option performance-wise.

abiri
  • 95
  • 1
  • 1
  • 9

1 Answers1

0

You have to use an orderby clause. Without an orderby there are no guarantees about the order in which the data is fetched from the database. It just depends on what the query planner makes of it.

If RankingObject has a reference to the Object you could do something like select object from RankingObject where type = :object_type orderby rank.

https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-ordering

M.P. Korstanje
  • 10,426
  • 3
  • 36
  • 58
  • We tried to do it with order by, but the problem is that i cannot order them after a specific value, as the object doesnt have a rank-specific value, and the rankobject only contains the id of the object, not the object itself (to prevent data duplication and non-coherent data). However, one idea was to join the object with the rankingObject, whereby we only add the id of the rankingObject to the object and order after this id; is there really no easier way to keep the order of the inner query? – abiri Feb 10 '15 at 16:42
  • No, there is not. Remember that order is not guaranteed. Your database just happens to be doing it that way by chance. See amongst others: http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause. So you need an order by. What you do it on doesn't really matter. – M.P. Korstanje Feb 10 '15 at 19:27
  • Btw. I wasn't talking about storing the object in the ranking object. Just keeping a reference (in the database that ends up as the id of the object in the rankingobject table). If you have a reference to the ranked object in RankingObject and RankingObject is a hibernate entity, then you can do `select object from RankingObject where type = :object_type orderby id`. `id` is a special hibernate property that will always reference the identifier. – M.P. Korstanje Feb 10 '15 at 19:29