Hi,

I've got an application that builds a search query from a UI where users can choose to restrict by a given set of criteria and group by a given set of columns. Consider this database structure:

Widget: widgetId, widgetName, widgetType, ...
Category: categoryId, categoryName, ...
WidgetCategoryMap: widgetId, categoryId

Basically, there is a many-to-many relationship between widget and category, with the join table WidgetCategoryMap.

I've mapped this like so in the Widget mapping:



Note that I don't have a corresponding inverse relationship: in this case, getting a list of all widgets in a category is not so interesting (since there could be millions of them), but getting all the categories a given widget is interesting (there would be between 1 and 10).

In my search functionality, I'm using the Criteria API to build a query (man, I love that thing, having spent a previous lifetime building strings to send to JDBC). I have added restrictions using Expression.in(), .ge() etc. to query various aspects fo a widget. This works fine, and I can also do something like:



However, I now want to make it possible to group by categories as well. I have other gropuing projections e.g. on the simple value widgetType (which is an int id in the database, and is mapped one-to-many to a WidgetType class):



however, I can't work out how to group by the category. Something as simple as:



doesn't work. It returns as many rows as there are widgets, each containing (null, 1) (i.e. it's set the category to null and the count to 1).

I've been unable to find any examples of this type of mapping. I guess ultimately what I want is something like:



but it's not obvious to me how that relates to the object graph Widget ->* Category.

I'm not sure if this is easier with HQL or some element of hand-written SQL. If at all possible, I'd like to stay with the Criteria API as much as possible, since it makes building the conditional query sooo much easier than building strings. Any suggestions would be greatly appreciated.

Cheers,
Martin

I've come one step closer by doing this:



That basically returns two columns, categoryId and count, and they appear to be correct.

However, I'd like to get the full Category objects as the first column, not just ids id. I could always do a secondary lookup for the categories (after all, I'm likely to get most of them back) and then map the ids to category instances in code, but this seems clumsy.

Any suggestions still greatly appreciated,

Martin

*bump*

Can no-one come up with a way of doing this in Hibernate?



That is, find the count of widgets grouped by a field (c.categoryId) that is related to the thing being counted (w.widgetId) via a many-to-many relation on a join table?

Martin