JPA Join with aggregate function

Question

I am a beginner when it comes to JPA and I am struggling to write the proper entity classes for my database structure.

Firstly, here are my tables:

create table article (
  id varchar(100) not null primary key,
  title varchar(255) not null
)
create table article_provider (
  providerId varchar(60) not null,
  articleId varchar(100) null,
  isOnSale BOOL NOT NULL,
  constraint article_provider_articleid_fk foreign key (articleId) references article (id) on update cascade on delete cascade
)

As you can see I have a one-to-many relationship (every article can have 0..n providers). Now when accessing articles I would also like to know, if the article is on sale by any of it's providers. For this I would use the following SQL query:

SELECT article.*, MAX(article_provider.isOnSale) AS isOnSale FROM article
LEFT JOIN article_provider ON article.id = article_provider.articleId
GROUP BY article.id

How would I best go about fetching this type of result using Hibernate/JPA?

Answer 1

Your query is not right -- you use attributes in SELECT clause that are not in GROUP BY clause, but for example to select Article.id + MAX(whatever) you can use something like:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Article> query = builder.createQuery(Article.class);
Root<Article> root = query.from(Article.class);
Join<Article, ArticleProvider> join = root.join(Article_.provider, JoinType.LEFT);
query.multiselect(root.get(Article_.id), builder.max(sale));
query.groupBy(root.get(Article_.id));
// query will return collection of scalar attributes (ID + Number)
Object result = entityManager.createQuery(query).getResultList()

To select exactly Article.* + MAX(sales) you would need to use the above as a subquery and join again with Article entity.

But there are simillar of examples elsewhere: How do I write a MAX query with a where clause in JPA 2.0?

EDIT:

• another tip: https://www.thoughts-on-java.org/hibernate-tip-subquery-criteriaquery/
• correction/clarification: I have only assumed you want to use Criteria API where subquery and combining the results for Hibernate is probably the only way. But you may try to use JPA query language and/or different provider where subquery with multiple results can be used -- jpa eclipselink subquery in from clause