0
Table User{    
 String id;
 String name;
 Date created_at;
 Set<Subscription> key;
}

Table Subscription  {
  String key;
  Date expiry;
}

I have to find user using list of subscription key but since one key is having multiple user. I have to take only the first user which is created first.

My code :

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
                            CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);
                            Root<User> userRoot = criteriaQuery.from(User.class);
                            SetJoin<User, Subscription> subscriptionJoin= userRoot.join(USer_.key);
                            
                            criteriaQuery.where(criteriaBuilder.and(subscriptionJoin.get(.key).in(keys)));
                            criteriaQuery.orderBy(criteriaBuilder.desc(userRoot_.get(user_.CREATED_AT)));
Harshit Kapoor
  • 1
  • 1
    Please check this thread https://stackoverflow.com/questions/17825610/getting-first-row-of-table-by-criteria-query – pratap Mar 03 '22 at 06:35
  • I am passing list of subscription key, if I will use queryCriteria.setFirstResult(0); queryCriteria.setMaxResults(1); then I will get only 1 record every time. – Harshit Kapoor Mar 03 '22 at 07:02
  • I want to fetch only one user per subscription key as there can be multiple user for a single key. And I am passing key(1234, 2213). currently DB is returning 3 records because in DB there are two user having same key. But my expectation is I should get 2 record only. – Harshit Kapoor Mar 03 '22 at 07:04
  • there is another thread with probably same requirement. https://stackoverflow.com/questions/47914988/use-hibernate-criteria-api-to-return-first-row-of-each-group you need to add ``group by`` subscription id – pratap Mar 04 '22 at 05:54

0 Answers0