Hi Robert,
I just retried our aggregate functions example with the latest Cayenne
and it works, generating GROUP BY and correct columns. I guess there is
some problem with the mapping. if you send your DataMap XML file (or
just the part of it related to the entities in question) this should
help in diagnosing your problem.
On a side note, while DerivedDbEntities are not going away anytime
soon, now I tend to prefer using scriptable SQLTemplates as they are
much more powerful and less hassle to configure and use. In future
versions of Cayenne (after 1.1) we will allow using them instead of
DbEntities for object mapping. Right now this can be done manually, or
simply use DataRows instead of objects, as most aggregate data is
read-only display stuff anyway.
Andrus
On Jul 8, 2004, at 5:23 AM, Robert Zeigler wrote:
> I'm trying to get the hang of aggregate functions in cayenne and
> having some hangups.
>
> pertinent DB entities look like this:
>
> Object
> id (attribute)
> rankings (relationship between id and ranking->componentid)
>
> ObjectRating
> objectid (attribute) (primary key)
> userid (attribute) (primary key)
> rating (attribute) (Integer)
> ratingLevel(relationship between rating and a set of "RatingLevel"
> objects that define valid ratings, meanings, etc. to each level)
> object (see rankings relationship)
>
> I'd like to pull out a set of objects based on an average rating.
> I defined a derivedDbEntity from ObjectRating
>
> derivedObj:
> objectid (groupby, pk, mandatory),
> userid (pk, mandatory),
> rating (avg(..).
> ratedObject (relationship from objectid to object->id)
>
>
> Running a query (on mysql 4.0.20) which attempts to look at the
> average rating yields:
>
> Invalid use of a group function
>
> The query logger shows:
>
> SELECT DISTINCT t0.description, t0.id, t0.last_updated, t0.name,
> t0.notes, t0.submitted_on, t0.category, t0.license, t0.status,
> t0.submitterid, t0.type FROM Objects t0, Object_Ratings t1 WHERE t0.id
> = t1.objectid AND (avg(t1.rating) = ?)
>
>
> If I were writing straight sql, I would do something like:
>
> select t0.*, avg(t1.rating) as averageRating from t0 Objects left join
> t1 on t0.id = t1.objectid group by t0.id having averageRating = '3';
>
> How do I achieve the same effect via cayenne + the modeler???
>
> Thanks in advance,
>
> Robert
>
This archive was generated by hypermail 2.0.0 : Thu Jul 08 2004 - 12:00:12 EDT