Re: Urgent: Example for Report Query

[Aaron Longwell]

Ron,

Using your logic, I'm trying another (less efficient I know) method of 
accomplishing my task. Here's my code:

           Criteria criteria = new Criteria();
           criteria.addOrderBy("performances.payout", true);
           Query query = QueryFactory.newQuery(Band.class, criteria, true);
This code returns Band objects (on which I have an inefficient method to 
find average data for the Performance Collections). My code is working 
correctly, except OJB is giving me duplicate records. I currently have 5 
records in that table, and the first two alphabetically are getting 
repeated when I try to order by performances.Payout. This is unusual.

Thanks,
Aaron
Ron Gallagher wrote:

Aaron --

This should create the query you need.

       String fields[] = new String[]{
           /* 0 */ "Name",
           /* 1 */ "avg(Performances.Payout)",
           /* 2 */ "avg(Performances.Attendance)",
           /* 3 */ "ContactPerson",
           /* 4 */ "ContactPhone"};
       // Assemble the criteria.
       Criteria criteria = new Criteria();
       criteria.addGroupBy(fields[0]);
       criteria.addGroupBy(fields[3]);
       criteria.addGroupBy(fields[4]);
       criteria.addOrderByAscending("avg(Performances.Attendance)");
       // Assemble the query.
       ReportQueryByCriteria query =
           new ReportQueryByCriteria(
               Band.class,
               fields,
               criteria);
Then, just use the getCollectionByQuery method on the PB to get the results.

HTH

Ron Gallagher
Atlanta, GA
[EMAIL PROTECTED]
 

From: Aaron Longwell <[EMAIL PROTECTED]>
Date: 2003/07/21 Mon AM 11:53:46 EDT
To: OJB User <[EMAIL PROTECTED]>
Subject: Urgent: Example for Report Query
I cannot figure out how to do a report query. Here's my set up:

Class: Band
   Fields: Name, ContactPerson, ContactPhone
   Collection: Performances
Class: Performance
   Fields: Payout, Attendance
I need to do a report query that will give me 5 columns: Band.Name, 
Band.ContactPerson, Band.ContactPhone, Avg(Performance.Payout), 
Avg(Performance.Attendance).

I then need to order by Name, Attendance or Payout based on the user's 
selection.

In real SQL it would look something like this:

SELECT Band.Name, AVG(Performance.Payout) As Payout,  
AVG(Performance.Attendance) As Attendace, Band.ContactPerson, 
Band.ContactPhone

FROM Band

INNER JOIN Performance ON Performance.BandID = Band.ID

GROUP BY Band.Name, Band.ContactPerson, Band.ContactPhone
ORDER BY Attendance ASC
I have been thoroughly impressed with the quality of the OJB product, 
but I have had the hardest time learning how to use it using only the 
online documentation. I am willing to help out with documentation 
writing of examples to answer questions like mine above.

Thank you for your time,
Aaron Longwell


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
   



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


 



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]