Help with simple (hopefully) query

bchamp · **Posted:** Wed Nov 30, 2005 5:33 pm

I'm fairly new to Hibernate. I have looked over the docs at this site and googled around to see if anyone had an example similar to mine, to no avail.

I am trying to construct a query for the following simplified situation.

Suppose we have a table to track blog entries called BLOG_ENTRY. Each row in the table represents a single entry to a blog. Two relevant columns are BLOG_ID and POST_DATE. Suppose the table has the following data:

Code:

|------BLOG_ID----|----POST_DATE------|
           17          2005-11-15
           17          2005-11-29
           18          2005-11-22
           18          2005-11-30
           19          2005-10-02

I would like a query that pulls the record with the most recent POST_DATE for each unique BLOG_ID. I would like to get the following results:

Code:

|------BLOG_ID----|----POST_DATE------|
           17          2005-11-29
           18          2005-11-30
           19          2005-10-02

In SQL, I think the following would work:

Code:

SELECT *
FROM
    BLOG_ENTRY a
   ,(SELECT
       BLOG_ID
      ,MAX(POST_DATE)
   FROM
       BLOG_ENTRY
   GROUP BY
       BLOG_ID
   ) b
WHERE
    a.BLOG_ID = b.BLOG_ID
AND a.POST_DATE = b.POST_DATE

I tried the following HQL, but did not succeed:

Code:

from BlogEntry a, (select b.blogId, max(b.postDate) from BlogEntry b group by b.pubId) c where a.pubId = c.pubId

Maybe HQL does not allow a nested select. Any suggestions? Sorry for the newbie question. By the way, I'm not opposed to doing this with Criteria, but I also don't know how to construct the Criteria correctly.

pksiv · **Posted:** Wed Nov 30, 2005 6:19 pm

Code:

select a from BlogEntry a where a.postDate = (select max(b.postDate) from BlogEntry b where a.blogId = b.blogId)

bchamp · **Posted:** Mon Dec 05, 2005 2:03 pm

pksiv wrote:

Code:

select a from BlogEntry a where a.postDate = (select max(b.postDate) from BlogEntry b where a.blogId = b.blogId)

Sorry, I didn't get back to this sooner. I was out sick for a few days.

Anyway, thanks for your suggestion. I'm back working on this again. Using your suggested query, Hibernate throws an exception

Code:

org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ( near line 1, column 45

In the expanded query string that Hibernate actually executes, column 45 is the opening paren of the inner select. Are inner selects allowed? Do we have the syntax right?

By the way, we're using Hibernate 3.0.2.

pksiv · **Posted:** Mon Dec 05, 2005 2:14 pm

Hmmm. Not sure what to say. I actually took your example and wrote test code to ensure that I got the results you were expecting. It runs fine on my machine using MySQL.

Here is the code...

Code:

Query q = session.createQuery("select a from BlogEntry a where a.postDate = (select max(b.postDate) from BlogEntry b where a.blogId = b.blogId)");
      
List result = q.list();
for (Iterator i = result.iterator(); i.hasNext();) {
   BlogEntry blogEntry = (BlogEntry) i.next();
   System.out.println("BlogEntry: " + blogEntry.getId() + " " + blogEntry.getBlogId() + " " + blogEntry.getPostDate());
}
session.close();

And the generated SQL...

Code:

select blogentry0_.id as id, blogentry0_.blog_id as blog2_0_, blogentry0_.post_date as post3_0_ from blog_entry blogentry0_ where blogentry0_.post_date=(select max(blogentry1_.post_date) from blog_entry blogentry1_ where blogentry0_.blog_id=blogentry1_.blog_id)

bchamp · **Posted:** Mon Dec 05, 2005 2:20 pm

pksiv wrote:

Hmmm. Not sure what to say. I actually took your example and wrote test code to ensure that I got the results you were expecting. It runs fine on my machine using MySQL.

Okay, thanks for your help. You definitely went beyond the call to help a newbie. This must mean I'm doing something wrong. I will redouble my efforts to see where I went wrong.

pksiv · **Posted:** Mon Dec 05, 2005 2:27 pm

bchamp wrote:

Okay, thanks for your help. You definitely went beyond the call to help a newbie. This must mean I'm doing something wrong. I will redouble my efforts to see where I went wrong.

I was a newbie once.... and working out other peoples problems on this forum has helped me a great deal in learning the product.

Are you using the correct dialect ? I performed my tests using MySQL.

bchamp · **Posted:** Wed Dec 07, 2005 6:48 pm

I spent some time reworking my environment so I could run this locally under my own control instead of under some else's environment remotely.

Good news is, the query works now, sort of. The problem is I got fewer results than I think I should be getting.

First, let's make this example as much like my real scenario as possible. Let's amend the definition of the BLOG_ENTRY table like this:

Code:

|------BLOG_ID----|----POST_DATE------|-----POS_STATUS-----|-----POST_TYPE-----|
           17          2005-11-15               1                    3
           17          2005-11-29               1                    3
           18          2005-11-22               1                    3
           18          2005-11-30               1                    3
           19          2005-10-02               1                    3

The following code yields 22 rows.

Code:

Query q = session.createQuery("select a from BlogEntry a where" +                                                                                                                    "a.postDate = (select max(b.postDate) " +
                                                "from BlogEntry b " +
                                                    "where a.blogId = b.blogId " +
                                                        "and a.postStatus = 1 " +
                                                        "and a.postType = :postType)");
q.setLong("postType", postType); // postType is 3
result =  q.list();

I also tried this HQL which yields 22 rows:

Code:

select a from BlogEntry a where a.postDate = (select max(b.postDate)
from BlogEntry b 
where a.blogId = b.blogId) and a.postStatus = 1 and a.postType = :postType

The following SQL code yields 61 rows when I run it directly in MySQL Query Browser against the same DB.

Code:

SELECT * FROM 
BLOG_ENTRY ENTRIES
,(SELECT BLOG_ID, MAX(POST_DATE) 
FROM BLOG_ENTRY
GROUP BY BLOG_ID) BLOGS 
WHERE 
 ENTRIES.BLOG_ID = BLOGS.BLOG_ID 
AND ENTRIES.POST_STATUS = 1 
AND ENTRIES.POST_TYPE = 3

So, I'm pleased that now have valid syntax, but I still need to figure out why I'm not getting 61 rows like the SQL does.

Any ideas?

bchamp · **Posted:** Mon Dec 12, 2005 3:40 pm

Success! The Hibernate query you gave me was indeed correct. The SQL I was given was incorrect and I was judging the Hibernate query by the SQL query. Sorry for the confusion. Just like a newbie, huh?

Thanks for your excellent help.