Hi people,

Quick question. I have a Company entity which uses cmr to define a one-many relationship to Employee entities. What's the best way of obtaining for example the top 10 earning employees for a specific company.

The collection will be for external consumption by session beans.

I keep seeing the same ejb-ql examples over and over! Sorry if it's a dumb question.

I thought I'd clarify a little more.

A finder method on the employee entity would be a solution, but in effect it throws away the relationship encapsulated by cmr. Is this the usual practice is what I'm asking.

A business method which uses a select method internally might work also I guess. More intuitive? Seems counter-intuitive to pass lots of variables into an employee finder that might be specific to the company entity's needs (it's purely for managing the relationship - not a general "find" requirement).

It must be a common need to retrieve sub-portions of a cmr collection, but I haven't really seen a best practice for doing it. Where is order by? :-(

Hi,

your requirement, as i understand it, cannot be solved entirely with OQL, which one would expect. One of the main reasons being, that OQL in its current version does not support an ORDER BY or a GROUP BY clause, see also http://www.c2.com/cgi/wiki?EjbQueryLanguage and also http://www.c2.com/cgi/wiki?EjbQueryLanguageFlaws. One possible workaround could be to classify the employees and have a WHERE clause for the classification, or have something like the following:

<ejb-ql>SELECT OBJECT (e) FROM Employee e, Company c
WHERE c.id = ?1 AND e MEMBER OF c.employees</ejb-ql>

And read from the collection and pick the 10 highest earning ones.

Christoph