(N + 1) selects problem with child Map-collection

friends! I have these entities:

Document:

@Entity
@Table(name = "documents")
public class Document extends AbstractNamedEntity {

....
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "document_change_notices",
            joinColumns = @JoinColumn(name = "document_id"),
            inverseJoinColumns = @JoinColumn(name = "change_notice_id"))
    @MapKeyColumn(name = "change")
    private Map<Integer, ChangeNotice> changeNotices;
....

}

and ChangeNotice:

@Entity
@Table(name="change_notices")
public class ChangeNotice extends AbstractNamedEntity {

....
    @ElementCollection(fetch = FetchType.LAZY)
    @CollectionTable(name = "document_change_notices", joinColumns = @JoinColumn(name = "change_notice_id"))
    @MapKeyJoinColumn(name = "document_id")
    @Column(name = "change")
    private Map<Document, Integer> documents;
....

}

And these are my repositories:

For Document:

public interface DocumentRepository extends JpaRepository<Document, Integer> {

....
    @Query("select d from Document d left join fetch d.changeNotices where d.decimalNumber=:decimalNumber")
    Optional<Document> findByDecimalNumberWithChangeNotices(@Param("decimalNumber") String decimalNumber);
....

}

and for ChangeNotice:

public interface ChangeNoticeRepository extends JpaRepository<ChangeNotice, Integer> {

....
    @Query("select c from ChangeNotice c left join fetch c.documents where c.id=:id")
    Optional<ChangeNotice> findByIdWithDocuments(@Param("id") int id);
....

}

So, when i want to get Document with changeNotices, thats not a problem, i have only one select. But when i want to get ChangeNotice with documents – i have (n + 1), first for document, and n for changeNotices Map.

I use join fetch in my query, but it does not help. I think the problem is that in Document i have Map<K,V>, where entity is a value, and i should use @ManyToMany relationship. And in ChangeNotice i have Map<K,V>, where entity is a key, and i should use @ElementCollection.

Are there any ways to write a query that select ChangeNotice with documents in one select? (without changing my entities code, may be small fixes possible)

Answer

So, a lot of time has passed, i didn’t find an answer. But it is my architecture issue. I had to use another class, that contains Document, ChangeNotice, and Integer field. My Document and ChangeNotice entities had child collection of this class with @OnetoMany relationship. It solves the problem.

Advertisement

Answer