Here's something I'm grappling with. I've got a db and have mapped it with Hibernate. Now I have a new requirement on an existing screen.

I've got a Company entity. It has Products. These products have Uses. The Products have to be approved for each Use separately. Each Approval has a date associated with it.

I want to query my db for all the Companies that have a Product for which a Use has an Approval in a certain time period, and along with each Company, The new requirement is that I want to know the # of Approvals that it garnered during that time period. I need this # plus several fields from the Company object.

Another way to say it is that I want to be able to print out something like the following:

for the period from xx/xx/xx to xx/xx/xx

Company A with ticker ARTY had 3 approvals
Company D with ticker DOUG had 1 approval
Company E with ticker ELSA had 6 approvals
Company J with ticker JOEY had 2 approvals

How can I do this simply? Before the new requirement, I had a query like this that returned to me all the companies I needed:

select company
from Use use
join use.product.company as company
join use.approvals as approval
where approval.date >= :startDate and approval.date < :endDate

Now it just so happens that I ran this query on months where no company had more than one approval, so I never noticed that the result set had each company listed once for each approval. So it turns out that I have the information I need; I can create a Map<Company, Integer> out of it and use that.

However, what I'd prefer is to do something like:

select company, count(company)
(from...join...where... as before)
group by company

But that's not legal; the resulting SQL is grouped by company.id but there are all the other fields of company in the select, so it fails.

How would I do what I want?

Now I might have misundestood your problem, but here is what I think.

If you run a query for companies joining in other stuff, the resulting list will still have as many elements as the underlying ResultSet, this results in the fact that you see a company multiple times, as they might have more than one joined row each.

So you can create a Set, and use that instead of the list, like this:

Before:


After:


But again, that's pretty much what you suggested. I don't think you can have it any easier than that.

The group by does not work of course if you fetch company data. But you could skip loading company data (other than the id), if you had most of it in the 2nd level cache, in case it is enabled in your environment. Then you would just fetch companies by ids from the cache with session.load() while you were iterating the query results. It all depends on your application, and your caching strategy.

Roland

Thanks Roland. I think you understand what's going on. The Set tip is useful.

I didn't mean to rate your posting "not helpful" - sorry about that! Post another msg here and I'll be happy to rate it helpful, as I intended to on that one.