How to page when mapping a value from OneToMany

[EvandoBlanco]

I'm trying to use an entity view to represent a list compiled from several related entities. When paging, mapping a value from a OneToMany field results in where clause of WHERE entity.id in (<subquery>). This results in more views being returned than I would expect: i.e. one for every entity within the limit + related entity. Is there another pattern I should be using?

In the case below, the resulting query will create a views for every kitten of the first 10 cats.

To recreate simply adjust the CatSimpleView from the sample as follows:

@EntityView(Cat.class)
public interface CatSimpleView {
    
    @IdMapping
    Long getId();

    String getName();

    @Mapping("kittens.name")
    String getKitten();
}

Run a query with:

CriteriaBuilder<Cat> builder = criteriaBuilderFactory.create(entityManager, Cat.class);
EntityViewSetting<CatSimpleView, PaginatedCriteriaBuilder<CatSimpleView>> setting = EntityViewSetting.create(CatSimpleView.class, 0, 10);
setting.addAttributeSorter("id", Sorters.ascending());
return entityViewManager.applySetting(setting, builder).getResultList();

Results in:

 select
        cat0_.id as col_0_0_,
        cat2_.name as col_1_0_,
        cat0_.name as col_2_0_,
        (select
            count(*) 
        from
            cat cat3_) as col_3_0_ 
    from
        cat cat0_ 
    left outer join
        cat_kittens kittens1_ 
            on cat0_.id=kittens1_.cat_id 
    left outer join
        cat cat2_ 
            on kittens1_.kittens_id=cat2_.id 
    where
        cat0_.id in (
            (
                select
                    cat4_.id 
                from
                    cat cat4_ 
                order by
                    cat4_.id ASC limit 10
            )
        ) 
    order by
        col_0_0_ ASC

[EvandoBlanco]

Doing some work, I see that this is the ID query caused by the join. Does this prevent properly paging when mapping a OneToMany?

[beikov]

You're "joining" over a collection kittens through your mapping expression, so it's only natural that you will get a row for every kitten.
If you want more structure, you will have to map the kittens as collection i.e.

@EntityView(Cat.class)
public interface CatSimpleView {
    
    @IdMapping
    Long getId();

    String getName();

    @Mapping("kittens.name")
    Set<String> getKittenNames();
}