I have a data model (simplified) that has customer to site (one to many). The sites have a start-date and stop-date field, and in the collection of sites retrieved from customer, I'd like to limit the sites to those that are "current", or current to an arbitrary date, like:
Code:
select customer.customer_id, site.site_id from customer, site
where site.customer_id = customer.customer_id
and site.start_date <= ?
and site.stop_date > ?
or ? could be "sysdate" (for Oracle, at least).
I already have my collection mapping set up so that I get all the sites for a customer (I know, congratulations...), but I'm trying to figure out the best way to limit the collection.
I can think of several ways: one, make a view in the database, which would make it impossible to use an arbitrary date, two, do it in code, or three, figure out how to do it with a mapping or a query. It appears that using a <subquery> on <class> might do what I want, but I'm not sure.
Is there any way to do this at runtime with an HQL query? Or is this a mapping-time thing?
Login
FAQ
Search