order by "property" is null ??

multi · **Joined:** Tue Jan 22, 2008 8:23 am **Posts:** 3

Hi everybody,
i don't find any information about this... so i will ask you for help :")

i need to create a query, something like that:

select * from users order by prop1 is null, laslogin asc

i try with hql, but i got an exception :

Quote:

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: is null

i didn't find a solution with Criteria ..

can anybody give me a hint ...

Thanks in advise!
George

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Code:

select * from users order by prop1 is null, laslogin asc

That "is null" doesn't make sense there

Nandem · **Posted:** Tue Jan 22, 2008 6:29 pm

try that:

Code:

select * from users where prop1 is null order by prop1, laslogin asc

maybe it works!! =)

multi · **Joined:** Tue Jan 22, 2008 8:23 am **Posts:** 3

Hi guys,
seems nobody understand my idea :)

i have a table with 3 columns for example:

Quote:

username varchar(255)
last timestamp
userInfo blob

so, every user have a "last" activity, when userInfo is null the user isn't registered yet, if is != null is registered,
and i want to order by last, but first to get all registered users (with != userInfo) and at the end not registered users.

Thanks, George

Nandem · **Posted:** Wed Jan 23, 2008 7:49 am

hm, i got the idea..

try make a union on queries..

try that:

Code:

select * from users where prop1 is not null order by prop1, laslogin asc 
UNION
select * from users where prop1 is null order by prop1, laslogin asc

it that doesn't resolve..

try make 2 calls, and after that join those two lists.

(i don't have idea if u use order by prop1 DESC maybe the null become to be in lasts place, but i do not have ever tried this things!)

cya

multi · **Joined:** Tue Jan 22, 2008 8:23 am **Posts:** 3

i did it, the problem is that i want to page the result, (with limit ..)
with little calculations i make it ;)

about the union -> you can't make a that query

Quote:

select * from users where prop1 is not null order by prop1, laslogin asc
UNION
select * from users where prop1 is null order by prop1, laslogin asc

must be only one "order by"

about the join, i will get duplicate values, so..

i hope my solution to be helpful to someone :)

Code:

   public int getUsersCount(){
      return (Integer)hs.findByDetachedCriteria(DetachedCriteria.forClass(User.class).setProjection(Projections.rowCount())).get(0);
   }
   
   public int getNotRegisteredUsersCount(){
      return (Integer)hs.findByDetachedCriteria(DetachedCriteria.forClass(User.class).add(Expression.isNotNull("uinfo")).setProjection(Projections.rowCount())).get(0);
   }
   public List getUser(int first, int max, String orderBy, String orderDirection){
      
      List result, result2 = null;
      
      DetachedCriteria criteria, criteria2 = null;
      if(orderDirection.equals("asc"))
         criteria = DetachedCriteria.forClass(User.class).add(Expression.isNotNull("uinfo")).addOrder(Order.asc(orderBy));
      else
         criteria = DetachedCriteria.forClass(User.class).add(Expression.isNotNull("uinfo")).addOrder(Order.desc(orderBy));
      
      hs.setFirstResult(criteria, first);
      hs.setMaxResults(criteria, max);
      
      result = hs.findByDetachedCriteria(criteria);

      log.debug("result size criteria1 = " + result.size());
      
      if(result.size() < max){
         if(orderDirection.equals("asc"))
            criteria2 = DetachedCriteria.forClass(User.class).add(Expression.isNull("uinfo")).addOrder(Order.asc(orderBy));
         else
            criteria2 = DetachedCriteria.forClass(User.class).add(Expression.isNull("uinfo")).addOrder(Order.desc(orderBy));
         
         if(!result.isEmpty()){
            int max2 = max - result.size();
            log.debug("result is not empty! first result = 0, max results = " + max2);
            hs.setFirstResult(criteria2, 0);
            hs.setMaxResults(criteria2, max2);
         }
         else{
            int first2 = first - getNotRegisteredUsersCount();
            log.debug("result is empty! first result = " + first2 + ", max results = " + max);
            hs.setFirstResult(criteria2, first2);
            hs.setMaxResults(criteria2, max);
         }
         result2 = hs.findByDetachedCriteria(criteria2);
         
         log.debug("result2 size = " + result2.size());
         result.addAll(result2);
      }
      
      log.debug("result size last = " + result.size());
      return result;
   }