aggregate functions+mysql invalid group error

From: Robert Zeigler (rdzeigl...arizona.edu)
Date: Thu Jul 08 2004 - 05:23:51 EDT

  • Next message: McDaniel, Joe R.: "RE: Filter a to-many relationship"

    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 - 05:23:53 EDT