Complex ORDER BY clause

fvitorc · **Joined:** Mon Sep 01, 2008 9:35 am **Posts:** 24

Hi all,

I am building a kind of social network application and I am having some trouble to get this query right. The thing is, I want the query to order the result by friends and not friends, so that the friends of the current logged user would come first.

I have a User entity and a Friendship entity. The Friendship entity is responsible for storing the social network graph, and it's class looks something like this:

Code:

class Friendship {
  private User user;
  private User friend;
  ...
}

and for the order by clause I have something like this:

Code:

select user from User user where ... order by user not in (select f.friend from Friendship f where f.user = :loggedUser)

But Hibernate is throwing [org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: in near line 1, column 50]. The problem is exactly in the (not in) part.

Does anyone no how to solve this issue??

Thanks in advance,
Vitor.

fvitorc · **Joined:** Mon Sep 01, 2008 9:35 am **Posts:** 24

By the way, Hibernate logs the following message:

Code:

18/11/2008 15:19:35 org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: <AST>:1:50: unexpected AST node: in

christianz · **Joined:** Wed Oct 08, 2008 1:54 am **Posts:** 14

From reading the query I'm unable to guess what the desired ordering would look like. Could you elaborate a bit on what do you want to fetch and in which order you'd expect the results?
However the syntax for a order by clause is something like this:
order by object0.property0 [asc|desc][, object1.property1 [asc|desc]]...

Edit:
Ok now I think I understand what you are aiming at - I'm not sure if it would be wise to do such ordering in a order by clause - perhaps you just write two queries (one matching the friends and one matchnig the rest) and return a lits containing both the result lists?

fvitorc · **Joined:** Mon Sep 01, 2008 9:35 am **Posts:** 24

The following nested select

Code:

(select f.friend from Friendship f where f.user = :loggedUser)

returns only the people that are considered friends of the logged user.

When I say "user not in (my friends)", I am saying to the RDBMS to return "false" (or 0) if the user is a friend of the logged user, and "true" (or 1) if the user is NOT a friend of the logged user.

So, using this order by clause, people who are considered friend of the logged user will come first, and people who are NOT considered friend of the logged user will come last.

I have tried using native SQL and it works.
But it would be improper to use native SQL in this case because it is a very complex query.

In this example I am only highlighting the problem I am having with the order by clause, so you can't see the complexity of the query.

Hope you understand what I want with this order by clause.

fvitorc · **Joined:** Mon Sep 01, 2008 9:35 am **Posts:** 24

christianz,

What you say could be a solution in some cases.
But in my case, since I will be using pagination, it wouldn't work.

Would it?

fvitorc · **Joined:** Mon Sep 01, 2008 9:35 am **Posts:** 24

For anyone who may have the same problem, I found a solution.

I registered a new function in my custom dialect as follows:

Code:

registerFunction("are_friends", new SQLFunctionTemplate(
            Hibernate.BOOLEAN, "?1 in (select f.user_id from friendships f where f.friend_id=?2)"));

Notice that it must be written in native SQL. So I am using the ids of the entities, and not the entities themself as usually used in HQL.

Now in my query I can use:

Code:

select user from User user where ... order by are_friends(user.id, :loggedUserId) desc

Thanks.

christianz · **Joined:** Wed Oct 08, 2008 1:54 am **Posts:** 14

I thnik my suggestion would also work if you use setFirstResult() and setMaxResults() for pagination.
Lets say you would like to retrieve result 100-150 from the complete result set - so you would first do the query for the users which "are friends" and only if you fetch less than (150-100) = 50 entries you would also query for the users which "are not friends" to fill up the result list.

Only if you receive zero results from the first query you would have to count the total number of results and adapt the second query to page as needed - however since the first query should be faster (only retrieving a limited number of friends) this should be fine. At least it should be better from a performance point of view considering the alternative being the invocation of a function which contains an 'in' check (especially on mysql those checks have lead to some strange performance issues afaik).

e.g. in HQL to fetch the Users 100-500 which have a field called city which should equal 'Berlin':

Code:

Query query = session.createQuery("SELECT user from User user left join Friendship friend with user.id = friend.friend.id where user.city = :cityname") AND friend.user.id = :currentUser");
query.setString("cityname", "Berlin");
query.setParameter("currentUser", loggedUser.id);
query.setFirstResult(100);
query.setMaxResults(50);
List subResult1 = query.list();

int toFetch;
int toStart = 0;
if (subResult1.isEmpty()) {
    toFetch = 50;
   // calculate where to start be fetching the amount of friends matching the query
} else {
    toFetch = 50 - subResult1.size();
} 

if (toFetch > 0) {
   query = session.createQuery("SELECT user from User user left join Friendship friend with user.id = friend.friend.id AND friend.user where user.city = :cityname AND friend is null");
   query.setFirstResult(toStart );
   query.setMaxResults(toFetch );
   [...]
}

I'm not sure about the correctness of the HQL since i just typed it in the browser and only translated it freely from SQL but something like that should work.

poroto20 · **Joined:** Tue Feb 03, 2009 2:49 pm **Posts:** 4

I need a complex order by, because the field to order by is not in the same table of the main entity. So I tried to register a function that returns that field.

These are the classes involved:

Code:

.....
@Table(name = "product")
public class ProductImpl extends RelationableImpl implements Product {

   private Map<String, Attribute> attributes = new HashMap<String, Attribute>();

   @OneToMany(targetEntity = AttributeImpl.class, fetch = FetchType.LAZY, cascade = CascadeType.ALL)
   @MapKey(name = "name")
   public Map<String, Attribute> getAttributes() {
      return attributes;
   }

   public void setAttributes(Map<String, Attribute> attributes) {
      this.attributes = attributes;
   }
....
}
......
@Table(name = "attribute")
public class AttributeImpl extends BaseObjectImpl implements Attribute {

   private String name;
   private String value;
............... (get and set)
 }

I've registered this function in my own MySql dialect:

Code:

......
registerFunction(
            "get_dynamic_attribute",
            new SQLFunctionTemplate(
                  Hibernate.STRING,
                  "SELECT att.VALUE FROM attribute AS att WHERE att.id IN (SELECT attributes_id FROM product_attribute WHERE product_id=?1) and att.name=?2"));

And I called the function above with this hql:

Code:

"select distinct............. order by get_dynamic_attribute(product.id,'vendor_part') asc"

I want s list of product order by a dynamic attribute called vendor_part, but when I run the code above I get this exception:

Code:

.......
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
.......
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT att.VALUE FROM attribute AS att WHERE att.id IN (SELECT attributes_id FRO' at line 1
.......

Anybody can help me and tell me what I'm doing wrong please??

Or, what can I do to get a list of products order in this way??