I believe this might be a useful link:
http://www.objectstyle.org/cayenne/userguide/fetch/sqltemplate.html
Tomislav
Dhruti Ramani wrote:
> 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!
> <http://us.rd.yahoo.com/mail_us/taglines/mobile/*http://mobile.yahoo.com/learn/mail>
> Check email on your mobile phone.
This archive was generated by hypermail 2.0.0 : Fri May 06 2005 - 10:22:36 EDT