Use EntityGraph for query with optional ManyToOne relationship

Hibernate ORM
1

I’ve got a Hibernate entity with an optional relationship to another entity:

@NamedEntityGraph(
    name = "expense-for-frontend",
    attributeNodes = {
      @NamedAttributeNode("shipment"),
    })
@Entity
public class Expense {
  ...
  @ManyToOne(fetch = FetchType.LAZY, optional = true)
  private Shipment shipment;
  ...
}

I want to be able to load Expense’s regardless of whether they have Shipment’s, but if they do have a Shipment it should come back pre-loaded to avoid DB round trips. The repository has a query along the lines of:

  @EntityGraph("expense-for-frontend")
  @Query(
      """
      SELECT e
      FROM Expense e
      LEFT JOIN Shipment s ON e.shipment.id = s.id
      WHERE (:status IS NULL OR e.status = :status)
      """)
  List<PaidShipmentExpenseBE> findAllFilteredShipmentExpensesWithPayment(
      @Param("status") @Nullable VendorPaymentStatus status);

The SQL this executes is:

select eb1_0.id, ...
from expense eb1_0
join shipment s1_0 on s1_0.id=eb1_0.shipment_id 
left join shipment sb1_0 on sb1_0.tenant_id = ? and eb1_0.shipment_id=sb1_0.id
...

The inner join against shipment is added by the @NamedAttributeNode("shipment") in the EntityGraph, and s1_0 is not used in the rest of the query. Because it adds an inner join, I can’t retrieve any Expenses that are not linked to Shipments while using that EntityGraph.

I can’t find any way to change the EntityGraph to allow for an optional ManyToOne relationship.

  • I do not want to remove @NamedAttributeNode("shipment") from the EntityGraph, because that results in an extra DB round trip per row of Expense, and there are a lot of them.
  • I can’t find any way to configure NamedAttributeNode to tell it to produce a left join.

This seems like it would a very common situtation, and the nullability of the column is exposed via the standard jakarta persistence annotation @ManyToOne(fetch = FetchType.LAZY, optional = true), so I would assume that an EntityGraph would handle this situation correctly. Am I missing something?

1 Like
2

Why don’t you use the association attribute in your query properly?

      SELECT e
      FROM Expense e
      LEFT JOIN e.shipment s
      WHERE (:status IS NULL OR e.status = :status))
3

Why don’t you use the association attribute in your query properly?

Clearly I didn’t know I was not using it properly. This is fantastic though!