Can't achieve proper behavior of Paging in Spring when using EntityGraph

Question

For last days I'm struggling with simple thing (I thought it should be simple). What I want to achieve:

Create an endpoint that will respond with page of products entities. Product entities need to have category and pictures metadata which are parent/child records.

To achieve above I have implemented NamedEntityGraph on entity which works as supposed fetching all required related entities avoiding N+1. The issue is happening when I'm trying to get Page of Products from repository and using mentioned EntityGraph. The issue is that I retrieve Page with wrong data. Example:

I have 5 products to retrieve. When page size is set to 2 I got:

"pageable": {
        "sort": {
            "empty": false,
            "unsorted": false,
            "sorted": true
        },
        "offset": 0,
        "pageNumber": 0,
        "pageSize": 2,
        "unpaged": false,
        "paged": true
    },
    "last": true,
    "totalPages": 1,
    "totalElements": 1,
    "first": true,
    "size": 2,
    "number": 0,
    "sort": {
        "empty": false,
        "unsorted": false,
        "sorted": true
    },
    "numberOfElements": 1,
    "empty": false

What I understand is that issue comes from joining other tables in query. I was looking for an answer but nothing found. How this is handled in Spring projects?

Entity

@Indexed
@Entity(name = "product")
@Table(name = "product")
@NoArgsConstructor
@Getter
@Setter
@AllArgsConstructor
@NamedEntityGraph(
        name = Product.WITH_CATEGORY,
        attributeNodes = {
                @NamedAttributeNode(value = "category"),
                @NamedAttributeNode(value = "mainPictureMetadata"),
                @NamedAttributeNode(value = "picturesMetadata")
        }
)
public class Product {

    public static final String WITH_CATEGORY = "graph.product.category";

    @Id
    @GeneratedValue(generator = "uuid2")
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    @Column(name = "id", nullable = false, updatable = false)
    @JdbcTypeCode(SqlTypes.VARCHAR)
    @Setter(AccessLevel.NONE)
    private UUID id;

    @Embedded
    private EntityAudit auditData;

    @Length(max = 512, min = 1)
    @FullTextField
    private String name;

    @Length(max = 5120)
    @Nullable
    @FullTextField
    private String description;

    @Column(nullable = false)
    @GenericField
    private Boolean published;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "category_id", referencedColumnName = "id")
    @IndexedEmbedded
    private Category category;

    @OneToOne
    @JoinColumn(name = "main_picture_metadata_id")
    private PictureMetadata mainPictureMetadata;

    @OneToMany(mappedBy = "product", fetch = FetchType.LAZY)
    private Set<PictureMetadata> picturesMetadata;
}

Repo:

public interface ProductRepo {

    Product save(Product product);
    
    @EntityGraph(value = Product.WITH_CATEGORY)
    Page<Product> findAll(Pageable pageable);

}

Service:

public Page<Product> selectProductPage(int pageNumber) {
        final Pageable pageable = PageRequest.of(pageNumber, 2, Sort.by("auditData.createdDate").descending());
                return repo.findAll(pageable);
          }

Answer 1

So the solution that I have implemented is pretty simple and effective. These are two queries - first paged select of ids only and then the second get by id without paging but with entity graph on:

public interface ProductRepo {

    @EntityGraph(value = Product.WITH_CATEGORY)
    List<Product> findByIdIn(Set<UUID> ids);
    
    @Query("SELECT p.id FROM product p")
    Page<UUID> findAllIds(Pageable pageable);
}

And service method:

public Page<Product> selectProductPage(int pageNumber) {
        final Pageable pageable = PageRequest.of(pageNumber, 2, Sort.by("auditData.createdDate").descending());
        final Page<UUID> pageOfIds = repo.findAllIds(pageable);
        final Set<UUID> ids = Set.copyOf(pageOfIds.getContent());
        final List<Product> products = repo.findByIdIn(ids);
        return new PageImpl<>(products, pageable, pageOfIds.getTotalElements());
    }