Join fetch multiple entities fails - query specified join fetching, but the owner of the fetched association was not present in the select list

Hibernate ORM
1

I have a query like this:

em.createQuery("SELECT "
    + "new burro.model.Receptacle(r.id, r.merchant, r.action, o, d, r.portDestination, COUNT(p.id)) "
    + "FROM products p  "
    + "JOIN p.receptacle r "
    + "JOIN FETCH r.origin o "
    + "JOIN FETCH r.destination d "
    + "WHERE r.closeDate IS NULL "
    + "AND r.dispatchDate is NULL "
    + "AND o.id = :origin "
    + "GROUP BY r.id, r.merchant, r.action, r.origin, r.destination, r.portDestination", Receptacle.class)
    .setParameter("origin", issuer.getId())
    .getResultList()

And I am getting this error:

java.lang.IllegalArgumentException: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=o,role=burro.model.Receptacle.origin,tableName=locations,tableAlias=location2_,origin=receptacles receptacle1_,columns={receptacle1_.origin,className=burro.model.Location}}]

I know I can avoid this error by removing FETCH from the JOINS but this introduces an N+1 problem which I am trying to avoid. My goal is to get all receptacles, products and locations within the same query.

How should I change the query so I can fetch r, p, r.origin and r.destination in the same query?

2

You are not selecting r, so you can’t fetch join on it. Just remove the FETCH keyword.

but this introduces an N+1 problem which I am trying to avoid

It shouldn’t. Please share your model.

You can also just pass r to the constructor as it should have the associations that you specified fetch joined. A possible problem is that the group by clause gets bigger though.

Apart from that, you can avoid the issue by not using FETCH and pass individual arguments to the constructor instead of the whole objects o and d.

I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Receptacle.class)
public interface ReceptacleDto {
    @IdMapping
    Long getId();
    String getMerchant();
    String getAction();
    String getPortDestination();
    @Mapping("SIZE(products)")
    long getProductCount();
    NodeDto getOrigin();
    NodeDto getDestination();

    @EntityView(Node.class)
    interface NodeDto {
        @IdMapping
        Long getId();
        String getName();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

ReceptacleDto a = entityViewManager.find(entityManager, ReceptacleDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: Blaze Persistence - Entity View Module

Page<ReceptacleDto> findAll(Pageable pageable);

or

List<ReceptacleDto> findByOriginIdAndDispatchDateIsNullAndCloseDateIsNull(long issuerId);

The best part is, it will only fetch the state that is actually necessary and handle the group by for you!

3

First I would like to see if I could solve my problem with JPQL and fetching r.origin and r.destination as objects or understand why it is not possible to do what I am trying.

This is my model:

@Entity(name = "products")
@Table(
		name = "products",
	    indexes =  {
	    		@Index(name = "fk_idx_product_receptacle", columnList = "receptacle", unique = false),
	    		@Index(name = "fk_idx_product_shipment", columnList = "shipment", unique = false),
	    		}
	)
@JsonIgnoreProperties(ignoreUnknown = true)
public class Product {

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "product_id_seq")
	@SequenceGenerator(name = "product_id_seq", sequenceName = "product_id_seq")
	private Long id;
...
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "receptacle")
	private Receptacle receptacle;
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "shipment")
	private Shipment shipment;
...

@Entity(name = "receptacles")
@Table(
		name = "receptacles",
	    indexes = {
	    		@Index(name = "fk_idx_receptacle_origin", columnList = "origin", unique = false),
	    		@Index(name = "fk_idx_receptacle_destination", columnList = "destination", unique = false),
	    		}
	)
public class Receptacle {

	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "receptacle_id_seq")
	@SequenceGenerator(name = "receptacle_id_seq", sequenceName = "receptacle_id_seq")
	private Long id;
...
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "origin")
	private Location origin;
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "destination")
	private Location destination;
...
	@Transient
	private long numProducts;

Please NOTE: I am not using @OneToMany mapping and I am not using Spring

I also tried passing r to the constructor:

em.createQuery("SELECT "
			    + "new burro.model.Receptacle(r, COUNT(p.id)) "
			    + "FROM products p  "
			    + "JOIN p.receptacle r "
			    + "JOIN r.origin o "
			    + "JOIN r.destination d "
			    + "WHERE r.closeDate IS NOT NULL "
			    + "AND r.dispatchDate IS NULL "
			    + "AND r.action = 'RTS' "
			    + "AND o.id = :origin "
			    + "GROUP BY r.id", Receptacle.class)
			    .setParameter("origin", issuer.getId())
			    .getResultList();

That query is not throwing an Exception but the constructor gets an org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor@1641aeb object
and hibernate generates multiple queries which looks like I not doing things right:

select receptacle1_.id as col_0_0_, count(product0_.id) as col_1_0_ from products product0_ inner join receptacles receptacle1_ on product0_.receptacle=receptacle1_.id inner join locations location2_ on receptacle1_.origin=location2_.id inner join locations location3_ on receptacle1_.destination=location3_.id where (receptacle1_.closeDate is not null) and (receptacle1_.dispatchDate is null) and receptacle1_.action='RTS' and location2_.id=? group by receptacle1_.id , receptacle1_.connote , receptacle1_.portDestination , receptacle1_.merchant , receptacle1_.origin , receptacle1_.destination , receptacle1_.action , receptacle1_.dispatchDate , receptacle1_.masterCrossDock , receptacle1_.closeDate , receptacle1_.deadWeight , receptacle1_.volumeWeight , receptacle1_.completionDate , receptacle1_.notes , receptacle1_.meta , receptacle1_.creationDateTime

select ... from receptacles receptacle0_ where receptacle0_.id=?

select ... from receptacles receptacle0_ where receptacle0_.id=?

select ... from receptacles receptacle0_ where receptacle0_.id=?

Another issue with this approach is that the select query is neither fetching r.origin nor r.destination

4

I think the problem is the usage of the constructor syntax. Hibernate before 6 can’t pass an initialized entity to the constructor. If you remove the constructor syntax and transform the objects manually, this should work.