Populate properties based on agg functions

bsiggelkow · **Posted:** Thu Sep 04, 2003 7:50 am

I have an application used for rating items. Each item has a zero-to-many logical relationship to a rating. (Each rating is associated to exactly one item and one user). A user can pick an item and then give it a rating between 0 and 3. Whenever I retrieve an item (or all items) I want to populate each Item object with the number of times it has been rated, and the sum of all the ratings. Currently I am retrieving the ratings for each item via a one-to-many mapping. Then I derive the numItems and totalRating values by looping through the set of ratings. This seems rather heavyweight and I am afraid it won't scale. Another option is to let the DB calc these values via HQL like the folloing:

Code:

select count(itemRating), sum(itemRating.value) from itemRating

The problem is I want to retrieve these values everytime I get the items. I was thinking I could use a join like:

Code:

select item, count(itemRating), sum(itemRating.value) from Item item, ItemRating itemRating where itemRating.item.id = item.id

The problem is that this forces me to manually walk thru the resulting list manually setting the number of ratings (the count), and total ratings (the sum) for each item -- this seems cumbersome. Is there a better way of doing this. Maybe it would be better to load the properties using 2 selects -- one for all items, and the other for all itemRatings -- however, this still forces me to merge the results in the middle-tier which I was trying to avoid. Anyone have any better ideas?

gavin · **Posted:** Thu Sep 04, 2003 7:55 am

Hibernate 2.1 solves this with the formula attribute.

gavin · **Posted:** Thu Sep 04, 2003 7:57 am

Oh. Actually, in your case, you should be able to use "select new Foo(....) ..." construct.

bsiggelkow · **Posted:** Thu Sep 04, 2003 1:57 pm

Thanks for the info, Gavin. I was able to get it to work using a query like the following:

Code:

select new Foo(foo,count(bar.value),sum(bar.value)) from Foo foo left outer join foo.bars group by foo

However, what I don't like about this is I had to create a constructor for Foo that takes a Foo, plus the count, and sum and copies the properties to the new instance.

tjonker · **Joined:** Wed Sep 03, 2003 6:08 pm **Posts:** 5

gavin wrote:

Hibernate 2.1 solves this with the formula attribute.

Where is this documented? I can't find anything in the reference that specifies the semantics of this attribute.

gavin · **Posted:** Wed Sep 10, 2003 9:33 am

Are you looking at the 2.1 doco? Its right there: section "4.1.9. property"

bsiggelkow · **Posted:** Wed Sep 10, 2003 9:51 am

I think an example here would help. Not being a heavy SQL user -- "arbitrary SQL expression" does not have a great deal of meaning to me. I think it is also important to understand that a property with a formula is a derived property -- that being said how does one know what would go in the formula -- is it column names or property names?

Also, why would I need a formula for a derived expression when I can do that in my object? (Unless, of course, the formula had SQL-only stuff it needed to do -- I am just not sure what that could be.)

Thinking out loud ...

tjonker · **Joined:** Wed Sep 03, 2003 6:08 pm **Posts:** 5

Ditto Bill's comment. The docs indicate the existence of the formula attribute, but no explanation of its semantics. What names are in scope? Can one use property names, or column names? How does it interact with other parts of the mapping?

mjek · **Joined:** Wed Nov 05, 2003 6:47 am **Posts:** 14

I had the similar problem - one of my object's properties is computed using a database function. This function accept several parameters, and my problem is that some of these parameters are known only at runtime. Is there any way to use the formula attribute so that one could leave some formula parameters unspecified (e.g. in JDBC style with '?')? These would not cause me to use constructs like 'select new Foo(...) from Foo'

gavin · **Posted:** Wed Nov 05, 2003 8:02 pm

No, the formula attribute cannot have parameters, and I can't imagine what an API would look like that would let parameters get values at runtime.

the "arbitrary SQL expression" may include columns of the table, sql function calls and subselects.

check the mappings in the Hibernate test package.

mjek · **Joined:** Wed Nov 05, 2003 6:47 am **Posts:** 14

May be, the listener pattern could be an appropriate API? For example, user could register the listener for the desired formula property at the Hibernate session. The listener interface would have a method onFormulaRead() which would be called whenever the formula property is about to be read from the DB. Inside of this method user could provide runtime parameters.

I admit that it could be an immature idea, but may be there is another way to solve the problem with customizing the generated SQL?

mjek · **Joined:** Wed Nov 05, 2003 6:47 am **Posts:** 14

By the way, when I try to define a mapping with a formula-based property I get the following exception:

net.sf.hibernate.MappingException: property mapping has wrong number of columns: net.sf.hibernate.mapping.Subclass.history
at net.sf.hibernate.mapping.PersistentClass.validate(PersistentClass.java:237)
at net.sf.hibernate.cfg.Configuration.validate(Configuration.java:555)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:712)
...

The property mapping looks like:

<property name="value" formula="myfunc(VALUE)"/>

This happens with Hibernate 2.1 beta 6. When I change the mapping to

<property name="value" column="VALUE"/>

everything works. Is it a bug or wrong usage?

gavin · **Posted:** Thu Nov 06, 2003 12:09 pm

Ummm I guess you better submit this one to JIRA ... I can hardly believe it though, since this is working in the test suite and the code looks like this:

Code:

         Property prop = (Property) iter.next();
         if ( !prop.isFormula() && prop.getType().getColumnSpan(mapping)!=prop.getColumnSpan() ) {
            throw new MappingException( "property mapping has wrong number of columns: " + getClass().getName() + '.' + prop.getName() );
         }