Yes, generally speaking it is often easier to build reports using raw
SQL (this means using SQLTemplate in Cayenne).
Your concerns about performance using "vanilla" Cayenne objects are
valid. Performance problems can be addressed though (e.g. by using
prefetching), but I'd say there are often more compelling reasons to
go with raw SQL for reports. Here is a few:
* SQL is much better suited for aggregating normalized data than Java
(in other words it often takes orders of magnitude more code to
process an object graph in Java to achieve what a relatively simple
SQL query can do).
* A "row" in a report is almost always an aggregation of more than
one row of database data, so object identity (tied to a database
primary key), the cornerstone of ORM, is lost.
It is still up to you to decide which way to go with your report. The
point I am trying to make is that you shouldn't be afraid to use SQL
when it is appropriate to do so.
Andrus
On Nov 7, 2005, at 11:37 PM, Dhruti Ramani wrote:
> We have to generate a report about deals and the deals have lots of
> sub objects in them. It would be great to bring back a collection
> of deals to iterate over but to get some info from the sub objects
> of that deal at the same time. For example, there are cash flows
> associated with quotes associated with the deals. I fear that if I
> bring back 2000 deals then ask for the quote on each deal and the
> cash flows on each quote (the de-referencing causing new queries)
> and then find the first and last cash flow to put into the report,
> that this will involve to many individual queries.
>
> I was reading information about this and feel like following link
> is related.(SQLTemplate).
> http://www.objectstyle.org/cayenne/api/cayenne/org/objectstyle/
> cayenne/query/SQLTemplate.html
>
> Am I in right direction? If not then can anybody suggest right answer?
> Thanks,
> Denna
This archive was generated by hypermail 2.0.0 : Tue Nov 08 2005 - 02:18:14 EST