Denna,
Aggregate queries are always a stretch with ORM, so instead of
inventing our own SQL dialect to do them, a "preferred" Cayenne
solution is to a use SQLTemplate query.
Your concern about having a DB-specific SQL in your app is valid,
however SQLTemplate addresses it pretty well. If you tried creating
SQLTemplates in the Modeler, you may have noticed that each such query
allows alternative SQL syntax to be stored under different adapter
keys. So each target database can have its own SQL "flavor" without any
dependencies in the Java code.
Finally, here is an alternative solution... To make it even more
transparent to Cayenne, create a "bucket" view in PostgreSQL and map it
as a read-only entity. But then you'll have to maintain DB view schema,
so it is probably not worth it...
Andrus
On May 5, 2005, at 7:11 PM, Dhruti Ramani wrote:
> We need to be able to do a complex query to aggregate cash flows.
> We have the following object model.
> --> Portfolios contain Deals
> --> Deals contain quotes
> --> One of the quotes, at most, is approved = true
> --> Quotes contain cashflow objects which have a date and a money
> amount in them.
>
> We need to be able to aggregate the cashflows by month, quarter and
> year.
>
> Here is an example of how we did it in SQL to run on postgresql. (it
> aggregates by year)
>
> select c.year,SUM(c.payamount) from cashflow c,deal d,quote
> q,portfolio p WHERE (c.quote_id=q.quote_id AND q.isacceptedquote='f')
> AND (q.deal_id=d.deal_id AND d.unsellable like 'No' AND
> d.pr_id=p.pr_id AND p.name like 'P1') group by year;
>
> Actually we need to get this result back as a collection of "Bucket"
> objects into Java.
> How can we do this in cayenne?
> We would rather not have database dependent sql in our application but
> it is not a disaster if we do.
>
> Thanks,
> Denna
This archive was generated by hypermail 2.0.0 : Thu May 05 2005 - 21:41:08 EDT