JPA: Returning an object and count of related objects (e.g. mailbox & unread messages)

[Rico]

Hi,

I have two model objects: Mailbox and Message. I'm trying to query for
all mailboxes that have unread messages _and_ returning the count of
unread messages at the same time.

I'm so close, but can't get the final step to work. Here is my
simplified model:

public class Mailbox extends Model {
public String name;
public List<Message> messages;
}

public class Message extends Model {
public String subject;
public Boolean isUnread;
}

Here is what works:

JPA.em().createQuery("select mbx.name, count(message.id) from
Mailbox mbx join mbx.messages as message where message.isUnread = true
group by mbx.name");

The above query returns a list with a string (mailbox name) and an
integer (unread message count).


However, trying to get the query to return Mailbox objects does not
work:

JPA.em().createQuery("select mbx, count(message.id) from Mailbox
mbx join mbx.messages as message where message.isUnread = true group
by mbx");

It fails with:

A javax.persistence.PersistenceException has been caught,
org.hibernate.exception.SQLGrammarException: could not execute query

I'm running this against the test server, if that makes a difference.

Any suggestions are welcome!

Thanks,
Rico

[Ricardo Nascimento]

Hello,

The group by clause must be the same that the select (except aggregate
fonction like count) but JPA (or Play???) transform :

group by mbx

on :

group by mbx.id

Try :

JPA.em().createQuery("select mbx.id,mbx.name, count(message.id) from

Mailbox
mbx join mbx.messages as message where message.isUnread = true group

by mbx.id,mbx.name");

Off course query will not return Mailbox object

[Rico]

Thanks for pointing me into the right direction! I investigated a bit
more and it turns out that it is Hibernate that replaced "group by
mbx" with "group by mbx.id".

Your query works. In addition _it is possible_ to return the Mailbox
object. Looking at the Hibernate documentation gave the final clue:

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-grouping

If you say something like "SELECT mbx FROM Mailbox GROUP BY..." you
are essentially including all Mailbox properties in the select clause
implicitly. GROUP BY requires that all SELECT properties must be
included so you have to explicitly list them (except OneToMany mapped
properties).

Here is the example from the Hibernate documentation for reference:

select cat
from Cat cat
join cat.kittens kitten
group by cat.id, cat.name, cat.other, cat.properties

Note the last line: it includes ALL cat properties so that the Cat
instance can be properly created.

Thanks for your help!
-Rico