Запрос JPA с подсчетом и группировкой (отношение «многие ко многим» с таблицей соединений)

У меня небольшая проблема. В моем приложении у меня есть две сущности, которые выглядят как на сниппетах:

//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  

меня не удовлетворяет. Я не могу вернуть авторов книги из WishList. Когда я помещаю wl.authorList перед появлением исключения EJB FROM:

...
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
...

Может ли кто-нибудь помочь мне создать априорный запрос? Можно ли это сделать в одном запросе?


person problemgenerator    schedule 01.12.2011    source источник


Ответы (3)


Я не думаю, что вы можете добиться этого, используя один запрос 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, вы можете для каждой записи выбирать авторов и возвращать такие подготовленные данные (популярность, название книги, категория, авторы).

Он, конечно, не получит самые высокие оценки в категории производительности, поскольку делает это в одном запросе, другого решения я не вижу на данный момент.

Кстати, вы уверены, что с вашей моделью все в порядке? У каждого WishList есть ровно одно название книги, категория и авторы? Разве данные книги не должны быть разделены на объект Book, а каждый WishList состоит из множества Books? Я думаю, тогда будет проще запросить WishLists, который состоит из тех же книг.

person Piotr Nowicki    schedule 04.12.2011
comment
Привет. Спасибо за ваш ответ. Я попытаюсь сделать это двумя отдельными запросами. (В моей модели есть сущность с именем Книга, но она предназначена для других целей) - person problemgenerator; 05.12.2011

Я не думаю, что вы можете группировать по 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
person James    schedule 05.12.2011

Я знаю, что это немного поздно, но работает ли запрос в других базах данных, таких как MySQL?

У Postgres было ограничение, и он сделал обязательным группировку по разделам всех столбцов, присутствующих в разделе выбора, - именно то, что говорит ошибка.

Начиная с v9.1, эта ошибка больше не должна возникать. :

Разрешить столбцы, отличные от GROUP BY, в целевом списке запроса, если первичный ключ указан в предложении GROUP BY [...] Стандарт SQL допускает такое поведение, и благодаря первичному ключу результат однозначен.< /эм>

person Xavier Portebois    schedule 27.07.2012