C-sentence not allowed for selected associations

Question

I have a query for the name as shown below, but keep getting the error message from Hibernate that it will not resolve my "with" clause since I am doing a join fetch.

I need to do a join fetch - because there is a related element, then I will not be present on this object.

    @NamedQuery(name = "Item.findItem", 
        query = "SELECT DISTINCT(c) FROM Item c " +                 
                "JOIN FETCH c.storeRelations as childStoreRelation " +                                                              
                "LEFT JOIN FETCH c.relatedItems as relatedRelations WITH relatedRelations.store.id = childStoreRelation.store.id " +
                "WHERE c.id = :itemId " +                                                                   
                "AND childStoreRelation.store.id = :storeId " +
                "AND childStoreRelation.deleted <> 'Y' " +
                "ORDER BY c.partnumber "),

The suggestion was to move the “c” part to my where clause, but that would lead to invalid results.

Consider an element A, which may have elements associated with it, but for some stores the relationship is not valid.

If you insert the where clause, then the main element will not be shown in the repository without relations, since SQL will be built according to Hibernate, therefore it is required that if any relations exist, they must be valid for the repository, otherwise nothing will be selected: - (

So, the classic fix found a lot of places (store.id is null OR store.id =: storeId) will not work.

Does anyone have another job?

I am running Hibernate 4.3.11

Thank you in advance

Answer 1

You need a keyword ON, not a keyword WITH.