computed properties

[Joe]

Using coldmvc I have a project model where I am struggling to
calculate the average rating for project with a computed property.

One project can have many ratings (like the stars people click to save
if they like it or not).

I tried following Vega's computed properties post, but I keep getting
empty string. As a work around I put a function on my project model to
calculate the rating, but I want to do this in a query so I can use it
in a list.

Can somebody tell me what I am doing wrong?

Here is his post:
http://www.danvega.org/blog/index.cfm/2010/9/7/ColdFusion-ORM-calculated-values

Here is my work around function on project.cfc.
function rating(){

var rating = 0;
var ratings = this.projectRatings();

for(var i = 1; i <= arrayLen(ratings);i++){
rating += ratings[i].rating().rank();
}

if(arrayLen(ratings) gt 0){
rating = rating / arrayLen(ratings);
}

return rating;

}

Here is my Project.cfc. Notice the formula attribute of the "rating"
property. That is the one I am struggling with.
/**
* @extends coldmvc.Model
* @persistent true
*/
component {

property id;
property name;
property projectRatings;
property name="rating" formula="select
sum(projectratings.rating.rank) / count(projectratings.id) from
Project project where project.id = id";

}

Here is my ProjectRating.cfc...the bridge table.
/**
* @extends coldmvc.Model
* @persistent true
*/
component {

property id;
property project;
property user;
property rating;

}

Here is my Rating.cfc
/**
* @extends coldmvc.Model
* @persistent true
*/
component {

property id;
property name;
property rank;
property projectRatings;
}

[Tony Nelson]

I think formulas need to be raw SQL, not HQL. Can you paste in the
correct SQL that you want to use so I can see it?

-Tony

On Oct 26, 10:16 pm, Joe <jban...@gmail.com> wrote:
> Using coldmvc I have a project model where I am struggling to
> calculate the average rating for project with a computed property.
>
> One project can have many ratings (like the stars people click to save
> if they like it or not).
>
> I tried following Vega's computed properties post, but I keep getting
> empty string. As a work around I put a function on my project model to
> calculate the rating, but I want to do this in a query so I can use it
> in a list.
>
> Can somebody tell me what I am doing wrong?
>

> Here is his post:http://www.danvega.org/blog/index.cfm/2010/9/7/ColdFusion-ORM-calcula...

[Joe]

I tried the following and it worked in SQL but not in the still not on
the property:

property name="rating" formula="
select pr.project_id, sum(r.rank) / count(pr.id) as rating
from project_rating pr
inner join rating r on r.id = pr.rating_id
where pr.project_id = id
group by pr.project_id";

I tried dumping project.rating() and its an empty string.
I also tried dumping project.getRating() and it shows undefined.

Any ideas?

[Tony Nelson]

If you're using .hbmxml files for your mappings, try putting the
formula in there. I don't think Hibernate is aware of the property
unless it's defined inside the mapping file.

-Tony

[Joe]

No luck. I tried to dump my query down to get a result but I still get
an empty string. Here's what I got:

Project.cfc
property name="rating" persistent = "false" formula="select
Count(pr.id) from project as p inner join project_rating as pr on
pr.project_id = pr.id where pr.project_id = p.id";

hib file
<property name="rating" type="string">
</property>

I also tried this site but it didn't work for me.
http://www.manjukiran.net/2009/07/18/coldfusion-orm-define-computed-properties-formula-attribute/#comments

[Tony Nelson]

If you're using an .hbmxml file, you'll need to specify all of the mapping properties inside the file. The properties inside the .cfc are merely used to generate the .hbmxml files that are required by Hibernate. If you're using your own .hbmxml file, ColdFusion/Hibernate will ignore any additional property mappings you might've specified inside the .cfc and only use what you have defined inside the .hbmxml file.

Long story short, try moving the formula definition from the .cfc to the .hbmxml file.

-Tony

[Joe]

Thanks, it worked when I put it in the .hbmxml file. I was missing the
() around the entire sql statement and I couldn't use the .get() in
ColdMVC, I had to use entityload():

var stuff = EntityLoad("Project",1,true);
writeDump("hit");writeDump(stuff.getRating());

<property type="int"
formula="(select count(project_rating.id) from project inner join
project_rating on project_rating.project_id = project.id inner join
[rating] on [rating].id = project_rating.rating_id where
project_rating.project_id = id)"
name="rating"/>

The above works, but it's not want I am trying to accomplish yet.

Now I am trying to use sum() in the SQL statement and its failing.
Example:

<property type="int"http://groups.google.com/group/coldmvc/
browse_thread/thread/e292293be4f759cf
formula="(select sum(rating.[rank]) / count(project_rating.id) from
project inner join project_rating on project_rating.project_id =
project.id inner join [rating] on [rating].id =
project_rating.rating_id where project_rating.project_id = id)"
name="rating"/>

Here's the error:
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid object name
'project0_.rating'.
Root cause :java.sql.SQLSyntaxErrorException: [Macromedia][SQLServer
JDBC Driver][SQLServer]Invalid object name 'project0_.rating'.

I am going to try to turn on query logging to figure out where
project0_ is coming from. As for not being able to use .get() in
ColdMVC I keep getting the following error:

could not execute query
The error occurred in C:\workspace\coldmvc\DAO.cfc: line 347

line 347 looks like this: var records = result.uniqueResult();

Any ideas?

On Oct 27, 8:46 pm, Tony Nelson <tonynelso...@gmail.com> wrote:
> If you're using an .hbmxml file, you'll need to specify all of the mapping
> properties inside the file. The properties inside the .cfc are merely used
> to generate the .hbmxml files that are required by Hibernate. If you're
> using your own .hbmxml file, ColdFusion/Hibernate will ignore any additional
> property mappings you might've specified inside the .cfc and only use what
> you have defined inside the .hbmxml file.
>
> Long story short, try moving the formula definition from the .cfc to the
> .hbmxml file.
>
> -Tony
>

> On Wed, Oct 27, 2010 at 9:38 PM, Joe <jban...@gmail.com> wrote:
> > No luck. I tried to dump my query down to get a result but I still get
> > an empty string. Here's what I got:
>
> > Project.cfc
> > property name="rating" persistent = "false" formula="select
> > Count(pr.id) from project as p inner join project_rating as pr on
> > pr.project_id = pr.id where pr.project_id = p.id";
>
> > hib file
> > <property name="rating" type="string">
> > </property>
>
> > I also tried this site but it didn't work for me.
>

> >http://www.manjukiran.net/2009/07/18/coldfusion-orm-define-computed-p...

[Joe]

Spoke with Tony Nelson offline and we actually got something to work
with the formula. Turns out I needed to alias all my tables in my
query. I also had to use
ormExecuteQuery() and not ormGetSession().createQuery(query). Here's
what I got:

<property type="float"
formula="(select (sum(r.rank)*100.0) / (count(pr.id) *100.0) from
project as p inner join project_rating as pr on pr.project_id = p.id
inner join rating as r on r.id = pr.rating_id where pr.project_id =
id)"
name="rating"/>

Now I am trying sort projects in a hql query by the computed property
and it thinks the value is a string in Hibernate. Here's the error
now.

Error while executing the Hibernate query.
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]
[SQLServer]The text, ntext, and image data types cannot be compared or
sorted, except when using IS NULL or LIKE operator.