"MultipleBagFetchException: cannot simultaneously fetch multiple bags" when joining three depth table

Question

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [Order.items, OrderItem.options];

Above is an exception i faced when i join three tables like below.

OrderItemOption.java

@Entity
public class OrderItemOption {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "item_option_id")
  private Long id;

  @Column(name = "item_id", nullable = false)
  private Long itemId;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(
      name = "item_id",
      referencedColumnName = "item_id",
      insertable = false,
      updatable = false
  )
  private OrderItem orderItem;
}

OrderItem.java

@Entity
public class OrderItem {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "item_id")
  private Long id;

  @Column(name = "order_id", nullable = false)
  private Long orderId;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(
      name = "order_id",
      referencedColumnName = "order_id",
      insertable = false,
      updatable = false,
      nullable = false
  )
  private Order order;

  @OneToMany(fetch = FetchType.LAZY, mappedBy = "orderItem")
  @OrderBy("item_option_id ASC")
  private List<OrderItemOption> options;
}

Order.java

@Entity
public class Order {
  @Id
  @Column(name = "order_id", nullable = false)
  private Long id;

  @OneToMany(fetch = FetchType.LAZY, mappedBy = "order")
  @OrderBy("item_id ASC")
  private List<OrderItem> items;
}

And here's my QueryDSL code to join them at one time.

final QOrder order = QOrder.order;
final QOrderItem item = QOrderItem.orderItem;
final QOrderItemOption option = QOrderItemOption.orderItemOption;

from(order)
.leftJoin(order.items, item).fetchJoin()
.leftJoin(item.options, option).fetchJoin()
.where(
    order.id.eq(orderId)
        .and(item.id.in(itemIds))
        .and(option.id.in(optionIds))
)
.fetchOne())

What i'm trying to do is to get Order object which contains filtered relationship, so that i can access filtered children via order object. and the type of relationship should be a List, not a Set.

for example, order.getItems().get(0).getOptions.get(0)

How can i achieve that goal?

Answer 1

To avoid above exception there are two possibilities:

1. Change List to Set
   or
2. Use List but do not fetch two bags. This means don't use fetchJoin() on both collections.

Filtering:

Using where conditions collections will be not filtered. Collections will contain all associated objects. Joining in JPA is for creating conditions on root object - Order. It is not the same as in SQL.

It is possible to filter associated collections using JPA 2.1 JOIN ON feature. This allows additional conditions in ON clause

see for example QueryDSL Left Join with additional conditions in ON

Answer 2

If you really can not use Set instead of List:

Parent.class

@OneToMany(
    mappedBy = "parent",
    orphanRemoval = true,
    cascade = { CascadeType.PERSIST, CascadeType.MERGE }
)
@OrderColumn(name = "position")
private List<Child> childs = new ArrayList<>();

Child.class

@ManyToOne(fetch = FetchType.LAZY)
private Parent parent;

And create a column in the Child's table named e.g "position"

ALTER TABLE child ADD COLUMN position integer NOT NULL default 0

And if you can not use other column in table, them you need to query the lists in sequence. Or use the id of the child and a custom getter.

@OrderColumn(name = "id_child", updatable = false, insertable = false)

public List<Child> getChilds() {
    childs.removeAll(Collections.singleton(null));
    return childs;
}