Thanks for replying.
Andrus Adamchik,
You said this in you email.
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.
Can you tell me how I can create SQLTemplate in modeler? Or anybody has some example for SQLTemaplate?
I am really sorry if I am being annoying here.
Thanks,
Denna
Andrus Adamchik <andru..bjectstyle.org> wrote:
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
---------------------------------
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
This archive was generated by hypermail 2.0.0 : Fri May 06 2005 - 10:08:22 EDT