Criteria eager fetch-joined collection to avoid n+1 selects

Question

Let's say Item and Bid are entities: an Item has many Bids. They are mapped in Hibernate in a typical parent/child relationship:

<class name="Item" table="ITEM">
  ...
  <set name="bids" inverse="true">
    <key column="ITEM_ID"/>
    <one-to-many class="Bid"/>
  </set>
</class>

How can I avoid n+1 selects when trying to access the bids of each Item after this query is executed?

List<Item> items = session.createCriteria(Item.class)
                        .createAlias("bids", "b").
                        .add(Restrictions.gt("b.amount", 100)).
                        .list();

Note I need an eager fetching for bids but with a further restriction on the collection (b.amount > 100)

I've tried the following unsuccessfully:

List<Item> items = session.createCriteria(Item.class)
                        .setFetchMode("bids", FetchMode.JOIN).
                        .createAlias("bids", "b").
                        .add(Restrictions.gt("b.amount", 100)).
                        .list();                        

List<Item> items = session.createCriteria(Item.class)
                        .createCriteria("bids")
                        .add(Restrictions.gt("amount", 100)).
                        .list();

http://stackoverflow.com/questions/617145/hibernate-fetching-strategy-when-to-use-join-and-when-to-use-select I think it is being asked before.... — Hayati Guvence, Oct 08 '12 at 22:12

score 11 · Answer 1 · edited Apr 24 '17 at 02:43

This criteria query seems right:

  List<Item> items = session.createCriteria(Item.class)
                    .setFetchMode("bids", FetchMode.JOIN)
                    .createAlias("bids", "b")
                    .add(Restrictions.gt("b.amount", 100))
                    .list();

FetchMode.JOIN is meant to solve n+1 problem. Have you defined some default_batch_fetch_size | batch-size anywhere in the mapping or configuration, which is reverse impacting?

If not, can you please try below HQL and see this solves your problem?

 Query query = 
      session.createQuery("from Item it left join it.bids b where b.amount=:bids");
 query.setParamter(bids, 100);
 List<Item> items = query.list();

score 7 · Accepted Answer · edited May 23 '17 at 12:02

This is an explanation of why adding a restriction on the fetch-joined collection causes the collection was not initialized (note that the same query without the restriction produce an eager fetch for the collection):

"If you have a 1:n relation between tables A and B, and you add a restriction to B and want to fetch A and B it eagerly, the question would be what happens when you want to navigate from A to B. Should you only see the data in B that matches the restriction, or should you see all Bs that are related to A?" see more here ...

However, using HQL instead of criteria, fetch partially the bids collections

List<Item> items = session.createQuery(
          "from Item i left join fetch i.bids b where b.amount > :amount")
          .setParameter("amount", 100)
          .list();

It seems to me an inconsistency, but it's how this works

By the way, if what you need is the list of parents and all its children, but just the parents whose children all meet certain restriction, so you may use this

List<Item> items = session.createQuery(
          "from Item i left join fetch i.bids b " +
          "where not exists (from Bid b where b.item = i and b.amount <= :amount)")
          .setParameter("amount", 100)
          .list();

This is a related posts: Hibernate query not returning full object.

score 2 · Answer 3 · edited May 23 '17 at 12:17

I think what you need is a Criteria that uses Subquery Exists on another Criteria. A similar answer is here: https://stackoverflow.com/a/15768089/1469525

DetachedCriteria criteria = session.createCriteria(Item.class, "i");
criteria.setFetchMode("bids", FetchMode.JOIN);

DetachedCriteria bidCriteria = DetachedCriteria.forClass(Bid.class, "b");
bidCriteria.add(Restrictions.gt("amount", 100));
bidCriteria.add(Restrictions.eqProperty("b.itemId", "i.id"));
criteria.add(Subqueries.exists(bidCriteria.setProjection(Projections.property("b.id"))));

score -1 · Answer 4 · answered Mar 12 '18 at 14:18

Try this code. Its solved my problem.

List<Item> items = session.createCriteria(Item.class)
                .setFetchMode("bids", FetchMode.SELECT)
                .createAlias("bids", "b")
                .add(Restrictions.gt("b.amount", 100))
                .list();

Criteria eager fetch-joined collection to avoid n+1 selects

4 Answers4

Linked