Getting complex queries in cayenne

From: Dhruti Ramani (dhrutiraman..ahoo.com)
Date: Thu May 05 2005 - 19:11:43 EDT

  • Next message: Dhruti Ramani: "Re: about Ordering in Cayenne"

    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 : Thu May 05 2005 - 19:11:45 EDT