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:
Code:
<set name="categories" table="WidgetCategoryMap">
<key column="widgetId"/>
<many-to-many column="categoryId"
class="Category"/>
</set>
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:
Code:
criteria.createCriteria("categories")
.add(Expression.in("categoryId", selectedCategries))
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):
Code:
ProjectionList projectionList = Projections.projectionList()
projectionList.add(Projections.groupProperty("widgetType"));
projectionList.add(Projections.alias(Projections.count("widgetId"), "NumWidgets"));
however, I can't work out how to group by the category. Something as simple as:
Code:
...
projectionsList.add(Projections.groupProperty("categories"))
...
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:
Code:
SELECT c.CategoryId, COUNT(w.WidgetId)
FROM Widget w INNER JOIN WidgetCategoryMap m ON w.WidgetId = m.WidgetId
INNER JOIN Category c ON m.CategoryId = c.CategoryId
GROUP BY c.CategoryId;
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