-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 10 posts ] 
Author Message
 Post subject: JPQL question about multiple counts
PostPosted: Mon Mar 09, 2009 6:21 pm 
Beginner
Beginner

Joined: Sat Feb 07, 2009 4:08 pm
Posts: 22
Location: New York City
Trying to write a simple JPAQL query that does three counts. The first two counts simply count all rows in the table, the third count does the same but with one condition.

Code:
select  count(distinct us), count(distinct u), count(distinct q) 
from UserSession us , User u, UserSession q 
where q.question.id is not null


I do not get any errors, but all three counts return zero, which is incorrect. Is this the right way to do this?


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 10, 2009 8:20 am 
Expert
Expert

Joined: Fri Jan 30, 2009 1:47 am
Posts: 292
Location: Bangalore, India
Well the query should be working. Can you post the SQL which is generated and the mapping of these classes?

_________________
Regards,
Litty Preeth


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 10, 2009 5:50 pm 
Beginner
Beginner

Joined: Sat Feb 07, 2009 4:08 pm
Posts: 22
Location: New York City
This is the sql that is output from Hibernate log:

Code:
    /* select
        count(distinct us),
        count(distinct u),
        count(distinct q)
    from
        UserSession us ,
        User u,
        UserSession q
    where
        q.question is not null */ select
            count(distinct usersessio0_.USERSESSION_ID) as col_0_0_,
            count(distinct user1_.USER_ID) as col_1_0_,
            count(distinct usersessio2_.USERSESSION_ID) as col_2_0_
        from
            USERSESSION usersessio0_,
            USER user1_,
            USERSESSION usersessio2_
        where
            usersessio2_.QUESTION_ID is not null limit ?



These are the two Entities (with relevant fields included only):
Code:


@Entity
@Table(name = "USER")
@org.hibernate.annotations.Entity(selectBeforeUpdate = true)
public class User implements Serializable, Comparable   {

   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   @Column(name = "USER_ID")
   private Long id;   
   
   @Column(name = "USERNAME", length = 20, nullable = false, unique = true)   
   private String username;
   
    @OneToOne(mappedBy = "user")
    private UserSession usersession;
   
   
       
   public User() {}

      ....etc
}



@Entity
@Table(name = "USERSESSION")
public class UserSession implements Serializable, Comparable   {
   
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO)
   @Column(name = "USERSESSION_ID")
   private Long id;   
   
   @OneToOne
   @JoinColumn(name = "USER_ID")   
   private User user;
   
   @OneToOne
   @JoinColumn(name = "QUESTION_ID")   
   private Question question;
   
    
         public UserSession() {}

     .... etc

}


I know the query is not returning what I expect because When there is one user in the user table, and that same user has an entry in the usersession table, the query should return 1,1,0 (Because usersession.question_id will be null). But the query is always returning 0,0,0. Obviously the first two counts are not correct.

What I want is simply:
A count of all rows in the user table
A count of all rows in the usersession table
A count of all rows in the usersession table where question_id is not null


I appreciate any help you can give to help me understand this better. Thanks!

John


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 11, 2009 12:34 am 
Expert
Expert

Joined: Fri Jan 30, 2009 1:47 am
Posts: 292
Location: Bangalore, India
Did u try running this sql directly on ur DB?

_________________
Regards,
Litty Preeth


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 11, 2009 5:22 am 
Senior
Senior

Joined: Wed Sep 19, 2007 9:31 pm
Posts: 191
Location: Khuntien (Indonesia)
I have tried your code. It returns the correct result, but is it compulsary to do query like this?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 11, 2009 6:23 pm 
Beginner
Beginner

Joined: Sat Feb 07, 2009 4:08 pm
Posts: 22
Location: New York City
Really? It worked for you? I wonder if it is RDBMS specific. I am using MySql. What database did you try it on?

It is not mandatory that I do it like that, I am just trying to find the best way to do these counts and want to know if it can/should be done in one query or in separate ones.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 11, 2009 6:28 pm 
Beginner
Beginner

Joined: Sat Feb 07, 2009 4:08 pm
Posts: 22
Location: New York City
littypreethkr wrote:
Did u try running this sql directly on ur DB?


Yes, I tried it on MySql 5.1 and it gets the wrong answer. I currently have it working by doing the first two counts in one query, and the third count (the one with the where clause) in its own query. It is only when I put the them together that it gets the wrong answer.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 11, 2009 7:25 pm 
Senior
Senior

Joined: Wed Sep 19, 2007 9:31 pm
Posts: 191
Location: Khuntien (Indonesia)
hmmmm,

I also use mysql 5.1. But I tried with 2 query only, using where condition also. It can run.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 11, 2009 7:26 pm 
Senior
Senior

Joined: Wed Sep 19, 2007 9:31 pm
Posts: 191
Location: Khuntien (Indonesia)
hmmmm,

I also use mysql 5.1. But I tried with 2 query only, using where condition also. It can run.

Have you tried running the query directly in your database?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 12, 2009 1:10 am 
Expert
Expert

Joined: Fri Jan 30, 2009 1:47 am
Posts: 292
Location: Bangalore, India
So probably its DB specific, coz i tried ur query on my DB (Oracle XE) and it was working. So it should be something to do with the DB. Try searching the DB forums.

_________________
Regards,
Litty Preeth


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 10 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.