JPA 2 + Criteria API + get oldest element in subquery

Janascho · **Joined:** Fri Aug 27, 2010 10:08 am **Posts:** 7

Hi,

I have a problem concerning the Criteria API. The same question was posted here, but I did not get any answer within 5 days, so I'll give it a try in this forum.

I have two entities (Dataset and Item), where the first one contains a list of the latter one.

Code:

@Entity
class Dataset {
    @Id long id;
    List<Item> items;
}

@Entity
class Item {
    @Id long id;
    @Temporal Date date;
    String someCriteria;
}

I am now looking for any dataset, which references an item with someCriteria = 'x' as oldest item in the dataset's list (it's date is before that of all others referenced by this dataset).

This is how it looks like right now:

Code:

Subquery<Item> _subquery = p_criteria.subquery(Item.class);
Root<Item> _itemRoot = _subquery.from(Item.class);
_subquery.select(_itemRoot);
      
// correlate
_subquery.correlate(_datsetRoot);
      
// type check
Predicate _typeP = p_builder.equal(_itemRoot.get(Item_.someCriteria), "x");

<additional predicates here>
...
...
...

// reference check
_subquery.where(_typeP);
      
return p_builder.exists(_subquery);

I though about creating a subquery, order it by date and check the first one. Unfortunately JPA-2.0 does not allow ordering in subqueries (or joins) (as far as I can see). My second idea was getting MAX(date), which should be supported by most of the databases. Same goes here, support for MAX, ABS, ... is only available for numbers.

Using a subquery, which is sorted, it would look like this (MSSQL):

Code:

SELECT d.id
FROM Dataset d
WHERE EXISTS (
    SELECT TOP 1 *
    FROM Item i
    WHERE i.dataset_id = d.FALL_ID
        AND i.someCriteria = 'x'
        ORDER BY i.date
)
ORDER BY d.FALL_ID

(How) could I do this in JPA-2.0?

Thanks in advanced.
Jan

Janascho · **Joined:** Fri Aug 27, 2010 10:08 am **Posts:** 7

I doubt that there is no way to achive such a result. Are there no ideas out there?

Janascho · **Joined:** Fri Aug 27, 2010 10:08 am **Posts:** 7

Thank you, that's it. I used Java 1.5 and therefor did not find those methods.

sanne.grinovero · **Posted:** Wed Nov 24, 2010 9:51 am

I had to delete the answer by user request as he was publishing his full email address by mistake;
it was saying:

Quote:

In criteria with java.util.Date fields, I successfully used CriteriaBuilder#least (instead of #min) and CriteriaBuilder#lessThanOrEqualTo (instead of #le).

thanks a.totev, hope you make a new account.