Menu
I am trying to fetch all users for a folder where the user was created after a certain date. the relationship between the user and the folder lives in a seperate table.
This is the query I came up with but it thorws the exception
No explicit selection and an implicit one cold not be determined
The code
@Override
public List<RetailPostUserTbl> getNewUsersForSiteSince( Date date, Integer siteId )
{
List<RetailPostUserTbl> toReturn = new ArrayList<RetailPostUserTbl>();
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
Class<RpUserFolderMapTbl> userFolderPC = userFolderMapDAO.getPersistentClass();
CriteriaQuery<RpUserFolderMapTbl> mapQuery = cb.createQuery( userFolderPC );
Root<RpUserFolderMapTbl> root = mapQuery.from( userFolderPC );
Path<Integer> folderIdPath = root.get( RpUserFolderMapTbl_.folder ).get( FolderTbl_.folderId );
Predicate folderCondition = cb.equal( folderIdPath, siteId );
Subquery<RetailPostUserTbl> rpSubQ = mapQuery.subquery( persistentClass );
Root<RetailPostUserTbl> subQRoot = rpSubQ.from( persistentClass );
Path<UserTbl> userPath = subQRoot.get( RetailPostUserTbl_.user );
Path<Date> userCreatedPath = userPath.get( UserTbl_.userCreateDate );
Predicate userCreateDateCondition = cb.greaterThanOrEqualTo( userCreatedPath, date );
rpSubQ.where( userCreateDateCondition );
mapQuery.where( cb.and( folderCondition, cb.exists( rpSubQ ) ) );
TypedQuery<RpUserFolderMapTbl> query = em.createQuery( mapQuery );
List<RpUserFolderMapTbl> results = query.getResultList();
for ( RpUserFolderMapTbl result : results )
{
RetailPostUserTbl rpuser = result.getUser().getRetailPostUser();
toReturn.add( rpuser );
}
return toReturn;
}
Anyone know why this is not working?
562
Implicit Inner Join With Single-Valued Association Navigation Inner joins can be implicit. As the name implies, the developer doesn't specify implicit inner joins. Whenever we navigate a single-valued association, JPA automatically creates an implicit join:
JPA doesn't provide right joins where we also collect non-matching records from the right entity. Although, we can simulate right joins by swapping entities in the FROM clause. 5. Joins in the WHERE Clause 5.1. With a Condition We can list two entities in the FROM clause and then specify the join condition in the WHERE clause.
JPA Join Types 1 Overview. In this tutorial, we'll look at different join types supported by JPA. ... 2 Sample Data Model. Let's look at our sample data model that we'll use in the examples. ... 3 Inner Joins. We'll start with inner joins. ... 4 Outer Join. ... 5 Joins in the WHERE Clause. ... 6 Multiple Joins. ... 7 Fetch Joins. ... 8 Summary. ...
Moreover, if we want to filter Phone entities in the WHERE clause, JPA won't allow that. This is because a path expression can't continue from a collection-valued association. So for example, e.phones.number isn't valid. Instead, we should create an explicit inner join and create an alias for the Phone entity.
You should set explicitly selection also for "subqueries".
rpSubQ.select(subQRoot);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
