Distinct query in Criteria NHibernate

Question

I need the corresponding query in Criteria language to this one (to retrieve all categories from my table but to distinct them):

SELECT DISTINCT categoryName 
FROM Category
WHERE CategoryID IN (
                        SELECT CategoryID 
                        FROM FoodCategory                       
                    )
ORDER BY categoryName

I have table FoodCategory table

    id   |     FoodID    | CategoryID
 --------|---------------|------------
         |               |  
         |               |  
         |               |

Actually CategoryID is a foreign key that is pointing to this table here. This is table for Category:

   CategoryID   |   categoryName   | otherField   
 ---------------|------------------|------------
                |                  |  
                |                  |  
                |                  |

And this is table for Food:

      FoodID    |     FoodName     | otherField   
 ---------------|------------------|------------
                |                  |  
                |                  |  
                |                  |

score 0 · Answer 1 · answered Jul 18 '14 at 12:11

Something like that should do the trick :

public List<String> retrieveFoodCategoryNames() {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<String> criteriaQuery = builder.createQuery(String.class);

    Root<FoodCategory> root = criteriaQuery.from(FoodCategory.class);
    // by default an inner join so it will only get the categories which have their id in the FoodCategory table
    Join<FoodCategory, Category> joinCategory = root.join(FoodCategory_.category);
    Fetch<FoodCategory, Category> fetchCategory = root.fetch(FoodCategory_.category);

    Path<String> categoryNamePath = fetchCategory.get(Category_.categoryName);
    criteriaQuery.select(categoryNamePath).distinct(true);

    criteriaQuery.orderBy(builder.asc(categoryNamePath));

    return entityManager.createQuery(criteriaQuery).getResultList();
}

This is not the exact same SQL request because you used a subquery where I'm using a join but it seemed more suited to this particular case. The subquery syntax is a bit more complex and I will not try to write it without compiling! ^^

If something is unclear let me know :-)

Distinct query in Criteria NHibernate

1 Answers1