Hi folks,
I've a problem with a group by clause in a HQL statement. My application is an EAR project using Seam, Hibernate and JPA on a JBoss AS. The entities are defined by Hibernate Annotations.
I'm not at work so I try to build an example that shows my problem. Let's assume I've an entity "Document" and "User". A document is created by an user called "editor". Every document is of a specific type. This would lead to something like this:
Code:
@Entity
@Table("document")
public class Document {
@Column("editor")
@ManyToOne(//don't know ATM)
private User editor;
@Column("type")
@ManyToOne
private Type type
//getters, setters and so on
}
@Entity
@Table("user")
public class User {
@Column("name")
private String name
//getters, setters and so on
}
@Entity
@Table("type")
public class Type {
@Column("name")
private String name
//getters, setters and so on
}
Now I want to create an overview of editors and the count of their documents of a specific type. In SQL this would be something like this:
Code:
select u.name, count(*) from user u, document d where u.id = d.editor and d.type = xyz group by u.name
I tried this in HQL:
Code:
select editor, count(*) from document group by user
The problem is that Hibernate generates a SQL statement like this:
Code:
select u.id, u.name, u.xxx from user u, document d where u.id = d.editor group by u.id
Of course this is an invalid combination of select and group by. Has anyone an idea how I could collect the data I need in a fast way (the solution I had was too slow)?
Thanks in advance
Jens
Login
FAQ
Search