Aggregate Functions Demo [Was: Re: Help with ExpressionFactory needed]

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Thu Oct 23 2003 - 14:25:32 EDT

  • Next message: Mike Kienenberger: "Error generating PK for entity 'DbEntity:"

    Hi Srdan,

    Ok, here we have an example of working with aggregate functions.
    Cayenne supports that via so called "derived DbEntities", but this is
    not documented. Originally I thought this solution will be just a
    temporary one, and did not want to expose this too much. Now that I
    played with it a bit more, I think it can be actually a good and clean
    one, if used properly. So here is a the download links for the
    aggregate functions demo that I created:

    A) Aggregate Functions Demo (will be included in cayenne-examples
    someday):

           
    http://objectstyle.org/downloads/cayenne/demos/cayenne-aggregate-
    functions-demo.tar.gz

    B) Latest Patched Cayenne (required by the demo):

          
    http://objectstyle.org/downloads/cayenne/patched/cayenne-src-
    1.0.1p4.tar.gz

    Now a few words about DerivedDbEntities and how to use them.

    1. Derived DbEntity: This is a logical equivalent of a database VIEW.
    It is an entity that is based on some other DbEntity. Derived
    DbEntities allow to use SQL expressions as column definitions and also
    support GROUP BY. A derived DbEntity can be created in the modeler
    using this toolbar button:

         
    http://objectstyle.org/cayenne/images/modeler/icon-derived-dbentity.gif

    2. Selecting from "derived" entities. Each derived DbEntity can be
    mapped to a read-only ObjEntity, that in turn can be used in queries.
    Since aggregate queries have a structure like "SELECT ... FROM ...
    WHERE ... GEOUP BY ... HAVING...", we essentially have 2 qualifiers -
    one for the "root" table (before GROUP BY) expressed as WHERE, and one
    - for the aggregated view, expressed as HAVING.

    SelectQuery reflects this logic. Main qualifier of such query will
    become a HAVING clause. But SelectQuery also defines so called "parent"
    qualifier. So SelectQuery.setParentQualifier() will build a WHERE
    clause. This also requires a call to
    SelectQuery.setParentObjEntityName()... E.g. in the example that I
    posted. There is an entity called Painting and one that is called
    PaintingStats, so for stats, "Painting" should be used as "parent" in a
    query.

    Hope this will get you started.

    Andrus

    On Wednesday, October 22, 2003, at 09:33 PM, Srdan Ivkovic wrote:

    > I was wondering if anyone could help with some basic expression
    > building
    > (using ExpressionFactory), here's what I'm trying to do..
    >
    > sql follows:
    >
    > select min(contactId) from contact
    > where contactName='Hacker'
    > and contactId > 19
    >
    > sql ends.
    >
    > The idea is that this query would get fired-off once user clicks on
    > 'next'
    > button (or something like that).
    >
    > all (any) help is greatly appreciated, my sql is really bad as I grew
    > up in
    > obscure, proprietary (non-sql) 4GL world ;-)
    >
    > Srdan

    This archive was generated by hypermail 2.0.0 : Thu Oct 23 2003 - 14:25:31 EDT