Hello, I have a question about using entity beans and aggregate functions together in one EJBQL query. For example I have a pet shop and I want to get all Pets (entity Pet) and their corresponding sales figures. A simple query like
select p, sum(...) from Pet p where ...
is only possible if I put every attribute of Pet in the group by clause. In my opinion this is bad design.If I need the information for just one Pet at a time I would just execute a second query. But what if I must show the information for every Pet in a large table?
In plain SQL I would just retrieve the fields of Pet which I need in that table. In EJB 2.1 I would execute the same native sql query and map the results into a special value object which contains only the attributes I need for that table. But what is the suggested way in EJB 3 where I shouldn't use value objects anymore?
What other possibilities are available? I think it is a very common problem retieving entities together with aggregate funtion. What solution for that kind of problem do you prefer? Is there a common pattern available?
Thanks in advance!