By Christian Beikov on 25 October 2017
In the last post I have shown examples of collection mappings and how to combine the powers of collections and subviews. This time I will show how to map subqueries in entity views with some common use cases and discuss possible alternatives.
It is a quite common requirement to display the count of children or latest element of some object and this is where a correlated subquery comes in. Just as you would use a subquery as select item, you can make use of a subquery mapping in entity views and the subqueries will be embedded just as expected.
Entity model
For simplicity I am going to use the post and comment data model again. Here a quick recap of it.
@Entity
public class User {
@Id
@GeneratedValue
private Integer id;
private String userName;
private String email;
private Date registrationDate;
// Other attributes
}
@Entity
public class Post {
@Id
@GeneratedValue
private Integer id;
private String text;
@ManyToOne(fetch = LAZY, optional = false)
private User poster;
@ElementCollection
@OrderColumn
private List<Comment> comments = new ArrayList<>();
}
@Embeddable
public class Comment {
private String comment;
@ManyToOne(fetch = LAZY, optional = false)
private User commenter;
}
Entity View model
One of the use cases as I already mentioned is the counting of children, so let’s count the comments for a post!
@EntityView(Post.class)
public interface PostWithCommentCountView {
String getText();
@MappingSubquery(CommentCountProvider.class)
Long getCommentCount();
class CommentCountProvider extends SubqueryProvider {
public <T> T createSubquery(SubqueryInitiator<T> subqueryInitiator) {
return subqueryBuilder.from("comments", "commentCountSub") (1)
.select("COUNT(*)")
.end();
}
}
}
| 1 | Correlated subquery for a collection of the parent query |
You probably already know of the SIZE function and for unconditional counting, it is not only the easier but also probably more performant choice.
@EntityView(Post.class)
public interface PostWithCommentCountView {
String getText();
@Mapping("SIZE(comments)")
Long getCommentCount();
}
As soon as you have the requirement of conditional counting or want to count something specific, a subquery is unavoidable for now. Let’s implement another use case that counts the different commenters of a post.
@EntityView(Post.class)
public interface PostWithCommenterCountView {
String getText();
@MappingSubquery(CommenterCountProvider.class)
Long getCommenterCount();
class CommenterCountProvider extends SubqueryProvider {
public <T> T createSubquery(SubqueryInitiator<T> subqueryInitiator) {
return subqueryBuilder.from("comments", "commentCountSub")
.select("COUNT(DISTINCT commentCountSub.commenter.id)")
.end();
}
}
}
Apart from using a correlated query root i.e. using the comments collection of the parent query root, it is also possible to use an entity as subquery root. One example use case for such a query is displaying the number of posts of a poster along with a post.
@EntityView(Post.class)
public interface PostWithPostCountView {
String getText();
@MappingSubquery(PosterPostCountProvider.class)
Long getPosterPostCount();
class PosterPostCountProvider extends SubqueryProvider {
public <T> T createSubquery(SubqueryInitiator<T> subqueryInitiator) {
return subqueryBuilder.from(Post.class, "postCountSub")
.select("COUNT(*)")
.where("postCountSub.poster").eqExpression("OUTER(poster)")
.end();
}
}
}
This will count all the posts that have the same poster as the outer poster. Here you can see the use of the OUTER function which is a way to refer to the outer query root from within subqueries. Careful, the OUTER semantics only relate to the query structure and is unrelated to the entity view hierarchy or nesting. This can be unintuitive in the context of entity views. E.g. when the PostWithPostCountView is used as a subview in another entity view A, the OUTER function refers to the entity that is backing view A instead of the entity behind PostWithPostCountView.
Behind the scenes
When using a subquery mapping like in the first example of PostWithCommentCountView you can always be sure to get a subquery like
SELECT
thePost.text,
(
SELECT COUNT(*)
FROM thePost.comments commentCountSub
)
FROM
Post thePost
Whereas the use of the SIZE function has room for optimizations. It can hoist the subquery into the parent query like
SELECT
thePost.text,
COUNT(*)
FROM
Post thePost
LEFT JOIN
thePost.comments comments_1
GROUP BY
thePost.id,
thePost.text
The queries for the other views look quite similar to the first one.
Conclusion
All we have shown up until now is the use of subqueries which return a single scalar value, but sometimes you might want to retrieve multiple values. Assume you want to show the previous post of some reference post with matching poster/author. One way to achieve this is to create a bi-directional or inverse mapping from poster to authored posts in the entity mapping. Such a mapping can then be used in the entity view. However, this approach is not always possible or desirable.
This is where correlated mappings come in to help. These mappings allow to correlate data that is not related by mappings in the entity model. Next time I’ll show you how you can map the described use case and what kind of different correlation strategies are possible along with their pros and cons.
Stay tuned!