Subqueries as Entity View attributes

Using scalar subqueries in Blaze-Persistence Entity Views

By Christian Beikov on 25 October 2017

Introduction

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!

blaze-persistence jpa entity-view subquery
comments powered by Disqus