Creating a pageable query where each entity has no more than N children

Question

I want to write a query that would fetch a page of artists with top 3 most listened tracks. Is it possible to accomplish this with a single repository query? Or would it be better to create a query fetching most popular artists first and then fetch top 3 tracks for each artist using separate queries?

Initially I wanted to accomplish it with a non-native query like this (which only fetches top popular artists by tracks and doesn't limit tracks):

@Query(value = "SELECT author FROM Artist artist JOIN artist.tracks track JOIN track.usersListenedTrack ult" +
        " WHERE ult.listenedAt >= :time" +
        " GROUP BY artist" +
        " ORDER BY COUNT(artist) DESC")
Page<Artist> findPopularArtistsByPeriod(Instant time, Pageable pageable);

But because non-native queries don't support LIMIT operator I guess I need to use a native sql (postgres in my case)

So, the query should look something like this:

@Query(native = true, value = "SELECT * FROM artist a " +
    " INNER JOIN artist_tracks at ON at.artist_id = a.id" +
    " INNER JOIN tracks t ON at.track_id = t.id" +
    " INNER JOIN (SELECT * from listened_tracks lt WHERE ?????) tmp ON tmp.track_id = t.id" +
        " WHERE lt.listened_at >= :time" +
        " GROUP BY artist" +
        " ORDER BY COUNT(artist) DESC")
Page<Artist> findPopularArtistsByPeriod(Instant time, Pageable pageable);

But I'm not sure how to select top 3 tracks from listened_tracks grouped by artist

So, my questions are:

Is it ok to fetch such data in one query?
What is the right way of querieng the listened_tracks table to fetch top 3 tracks by artist

score 1 · Answer 1 · answered Jan 18 '22 at 20:26

Answering your questions:

1 - I think it's ok to fetch this data in one query.

2 - One way you could do it is making a query to bring your top artists and then in your Java code filter the top 3. Another way would be to define the elements size of your page to 3, since the `limit` clause is not supported in hql. There are some ways to compare dates on HQL, you can use the BETWEEN X AND Y clause for example.

I found this post on stackoverflow about limiting the result, maybe it can help you! How do you do a limit query in JPQL or HQL?

score 0 · Answer 2 · answered Jan 19 '22 at 01:26

When the number of tracks per artist is small, it might not hurt a lot to just select the page of artists and fetch all of their tracks (either directly as part of the first query using e.g. join-fetch, or with a second query fetching batches of tracks). The tracks could then be easily sorted and filtered in java.

However, this can also be a real waste of resources, in case a larger amount of tracks is expensively loaded just to be discarded, since only the 3 most popular tracks are needed.

So in general, using a single DTO-projection query should be considered.

The problem here is that the projection of child-collections is not that simple:
Load child collection DTOs in JPA DTO projection query
Mapping JPA or Hibernate projection query to DTO (Data Transfer Object)

One convenient way to realize this would be to use blaze-persistance entity-views. It enables the simple definition and mapping of DTOs including collection-mappings.

A DTO could look like this:

@EntityView(ProjectionEntity.class) // <-- parent-entity
public interface ProjectionEntityView {

  @IdMapping
  Long getId();
  
  // only include the 3 'most popular' children:
  @Mapping(value = "children") // <-- associated collection
  @Limit(limit = "3", order = { "counter DESC" }) // <-- each child has a 'counter' prop
  List<ProjectionChildEntityView> getThe3MostPopularChildren(); // child-dto-projection
}

And it could be queried via a spring-data-repository:

public interface ProjectionEntityViewRepository
    extends Repository<ProjectionEntity, Long> {

  List<ProjectionEntityView> findAll(Pageable pageable);

}

Extension: Suppose that you would want to order the artists by their overall popularity (sum of the popularity of all their tracks), this calculated property could be included into the DTO by specifying a correlated subquery as part of the view interface:

@EntityView(ProjectionEntity.class)
public interface ProjectionEntityView {

@IdMapping
Long getId();

@Mapping(value = "children")
@Limit(limit = "3", order = { "counter DESC" })
List<ProjectionChildEntityView> getThe3MostCountedChildren();

// "overall popularity of the artist"
@MappingSubquery(ChildrenCounterSumSubqueryProvider.class)
Long getCounterSum();

class ChildrenCounterSumSubqueryProvider implements SubqueryProvider {
    @Override
    public <T> T createSubquery(SubqueryInitiator<T> subqueryBuilder) {
        return subqueryBuilder.from(ProjectionChildEntity.class, "child") //
                .select("SUM(counter)") //
                .where("child.parent.id").eqExpression("EMBEDDING_VIEW(id)")//
                .groupBy("child.parent.id")
                .end(); // (query-dsl is also possible)
    }
}
}

The calculated property can then be referenced as part of a Sort as usual:

Pageable pageable = PageRequest.of(0, 10, Sort.by(Order.desc("counterSum"), Order.asc("id")));
var page1 = parentViewRepository.findAll(pageable);

complete example w/ test

Creating a pageable query where each entity has no more than N children

2 Answers2