带计数和分组依据的JPA查询（与连接表的多对多关系）

Question

我有一个小问题。在我的应用程序中，我有两个实体，它们看起来像片段上的：

//imports, annotations, named queries
public class WishList implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "wishes_seq", 
                       sequenceName = "wish_list_id_seq", 
                       allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "wishes_seq")
    @Basic(optional = false)
    @NotNull
    @Column(name = "id")
    private Integer id;

    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 80)
    @Column(name = "book_title")
    private String bookTitle;

    @Size(max = 80)
    @Column(name = "book_cattegory")
    private String bookCattegory;


    @ManyToMany(cascade= CascadeType.ALL, fetch= FetchType.EAGER)
    @JoinTable(name="wl_authors", 
               joinColumns={@JoinColumn(name="wl_id")},
               inverseJoinColumns={@JoinColumn(name="author_id")})
    private List<Author> authorList; 

    // other methods

第二个实体：

//imports, annotations, named queries    
public class Author implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "authors_seq", 
                       sequenceName = "authors_id_seq", 
                       allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, 
                    generator = "authors_seq")
    @Basic(optional = false)
    @NotNull
    @Column(name = "id")
    private Integer id;

    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 58)
    @Column(name = "author_name")
    private String authorName;

    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 58)
    @Column(name = "author_surname")
    private String authorSurname;

    @Size(max = 58)
    @Column(name = "nationality")
    private String nationality;

    @Size(max = 64)
    @Column(name = "birth_place")
    private String birthPlace;

    @JoinTable(name = "wl_authors", 
               joinColumns = {@JoinColumn(name = "author_id",
                                          referencedColumnName = "id")},
               inverseJoinColumns = {@JoinColumn(name = "wl_id",
                                                 referencedColumnName = "id")})
    @ManyToMany(fetch = FetchType.EAGER)
    private List<WishList> wishListList;

    // other methods

如您所见，我准备的实体代表数据库中的两个表，它们是多对多关系（我使用了联接表）。我想统计 WishList 实体中具有相同作者、标题和类别的所有结果，并返回所有结果，如下所示：

1. 计数结果
2. 书籍标题
3. 书籍类别
4. 书籍作者

结果应按计数结果排序。 我准备的 JPA 查询：

SELECT Count(wl.bookTitle) AS POP, 
       wl.bookTitle, 
       wl.bookCattegory 
FROM WishList wl 
GROUP BY wl.bookTitle, 
         wl.bookCattegory, 
         wl.authorList 
ORDER BY POP DESC  

不能满足我的要求。我无法从愿望清单中返回该书的作者。当我将 wl.authorList 放在“FROM”EJB 之前时，会出现异常：

...
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function
...
Call: SELECT COUNT(t0.book_title), t0.book_title, t0.book_cattegory, t1.id, t1.author_name, t1.author_surname, t1.birth_place, t1.nationality FROM wl_authors t4, wl_authors t3, authors t2, authors t1, wish_list t0 WHERE (((t3.wl_id = t0.id) AND (t1.id = t3.author_id)) AND ((t4.wl_id = t0.id) AND (t2.id = t4.author_id))) GROUP BY t0.book_title, t0.book_cattegory, t2.id, t2.author_name, t2.author_surname, t2.birth_place, t2.nationality ORDER BY COUNT(t0.book_title) DESC
Query: ReportQuery(referenceClass=WishList sql="SELECT COUNT(t0.book_title), t0.book_title, t0.book_cattegory, t1.id, t1.author_name, t1.author_surname, t1.birth_place, t1.nationality FROM wl_authors t4, wl_authors t3, authors t2, authors t1, wish_list t0 WHERE (((t3.wl_id = t0.id) AND (t1.id = t3.author_id)) AND ((t4.wl_id = t0.id) AND (t2.id = t4.author_id))) GROUP BY t0.book_title, t0.book_cattegory, t2.id, t2.author_name, t2.author_surname, t2.birth_place, t2.nationality ORDER BY COUNT(t0.book_title) DESC")
Caused by: org.postgresql.util.PSQLException: ERROR: column "t1.id" must appear in the GROUP BY clause or be used in an aggregate function
...

有人可以帮助我创建适当的查询吗？是否可以在单个查询中完成？

Answer 1

我认为您无法使用一个 JPQL 查询来实现这一目标。也许，如果使用本机查询，您可能会实现它（不确定）。

考虑将问题分为两部分：

• 调用一个返回统计信息但没有作者的查询，
• 对于每个返回的行，获取作者。

因此，首先调用这样的查询：

SELECT DISTINCT COUNT(wl.bookTitle) AS POP, 
                wl.bookTitle, 
                wl.bookCattegory, 
                MAX(wl.id) 
FROM WishList wl 
GROUP BY wl.bookTitle, wl.bookCattegory, wl.authorList 
ORDER BY POP DESC

根据查询，返回的每个行将具有相同的作者。 MAX(wl.id) 仅用于获取您可以查询作者列表的这些 id 之一。

有了这个id，您就可以为每条记录获取作者并返回准备好的数据（受欢迎程度、书名、类别、作者）。

它肯定不会像在一个查询中那样获得性能类别中的最高排名，目前我没有看到其他解决方案。

顺便问一下 - 你确定你的模型没问题吗？每个 WishList 都只有一个书名、类别和作者？难道书籍数据不应该被分成一个 Book 实体并且每个 WishList 包含许多 Book 吗？我认为查询由相同书籍组成的 WishLists 应该更容易。

Answer 2

我认为您不能按 ManyToMany 进行分组，

请尝试，

SELECT Count(wl.bookTitle) AS POP, wl.bookTitle,  wl.bookCattegory, a.id FROM WishList wl join wl.authorList a GROUP BY wl.bookTitle, wl.bookCattegory, a.id ORDER BY POP DESC

Answer 3

我知道有点晚了，但是查询是否可以在其他数据库（例如 MySQL）上运行？

Postgres 有一个限制，并且在分组中强制选择部分中存在的所有列 - 正是错误所说的。

从 v9.1 开始，此错误不应再发生:

当在 GROUP BY 子句中指定主键时，允许查询目标列表中出现非 GROUP BY 列 [...] SQL 标准允许这种行为，并且由于有主键，结果是明确的。