I have two entities (Dataset
and Item
), where the first one contains a list of the latter one.
@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 should work with the Criteria API of JPA-2.0, but an answer as SQL-query would be fine, too.
This is how it looks like right now:
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>
...
// 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.
EDIT: Using a subquery, which is sorted, it would look like this (MSSQL):
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?