Josh --
My response is based on the following assumptions:
1) the 'weight' is defined as a property on the CarAddedParts class
2) The list of invoices that a customer has is provided by the 'invoices' property
3) The list of cars that are included on an invoice is provided by the 'cars' property
4) The list of additional parts for a car is provided by the 'additionalParts' property
5) All of the 'lists' in 2-4 are defined in your repository.
If those assumptions are correct, then the following will do what you're looking for.
PersistenceBroker broker = ...;
Criteria criteria = new Criteria();
criteria.addEqualTo("customerId", new Long(1));
ReportQueryByCriteria query =
new ReportQueryByCriteria(
Customer.class,
new String[]{"sum(invoices.cars.additionalParts.weight)"},
criteria);
Iterator iterator = broker.getReportQueryIteratorByQuery(query);
while (iterator.hasNext()) {
Object[] data = (Object[]) iterator.next();
// the sum will be in data[0]
}
if ((iterator != null) && (iterator instanceof OJBIterator)) {
OJBIterator ojbIterator = (OJBIterator) iterator;
ojbIterator.releaseDbResources();
}
Please note...both the compiler and unit tester on my email client are not working
today, so there may be some syntax errors.
Ron Gallagher
Atlanta, GA
[EMAIL PROTECTED]
>
> From: Josh Berry <[EMAIL PROTECTED]>
> Date: 2003/07/10 Thu PM 12:30:29 EDT
> To: <[EMAIL PROTECTED]>
> Subject: Optimization advice/help.
>
> I don't want this to just be a performance question, but I must admit that
> that is at the heart of my problem.
>
> First, a little background on my problem. I will try to work this into a
> standard shopping cart/customer problem, but it may prove difficult.
>
> So... Lets say I have the following classes:
>
> *Customer
> A person buying a car. Obviously many of these exist.
> *Invoice
> An individual record of a single customer buying one or more cars.
> Many of these can exist per customer.
> *CarInfo
> A total breakdown of a vehicle to be ordered.
> *CarMakeInfo
> Basic information on a broad category of vehicle. (e.g. Ford Mustang)
> *CarModelInfo
> Specific information on a vehicle.
> (e.g. 4.0 liter engine, manual locks, dimensions, etc.)
> *CarCustomizationsInfo
> Any special changes that a customer requested for the vehicle.
> This is basically a set of overrides to CarModelInfo. So, either
> it exists or it does not.
> (e.g. Dimensions changed due to added roof lights,
> power locks added.)
> *CarAddedParts
> Any of a number of additional items to be added to the vehicle.
> (e.g. the roof lights )
>
>
> Now... I believe these will be all that is needed.
>
> The relationships are as follows.
>
> Customer has a 1:M with Invoice.
> Invoice has a 1:M with CarInfo.
> CarInfo has a 1:1 with CarMakeInfo.
> CarInfo has a 1:1 with CarModelInfo.
> CarInfo has a 1:1 with CarCustomizationsInfo.
> CarInfo has a 1:M with CarAddedPartsInfo.
>
>
> So, the problem I have is this:
>
> We currently do reports where we need to know the total weight of everything a
> customer has ever ordered. Now, it is clean code with OJB, as I can just loop
> through all of a customer's invoices calling getWeight for every CarInfo.
> However, this has performed abysmally slowly.
>
> Now, my guess is that OJB does one select to get all of the Invoices. Then,
> it will do another select per Invoice to get all of the CarInfos.
> Then, for each CarInfo, it will do 3 selects to get each of CarMakeInfo,
> CarModelInfo, and CarCustomizationsInfo.
> In addition, it will do another select per CarInfo to get CarAddedPartsInfo.
>
> For a single customer with X invoices averaging Y CarInfos that have on
> average Z CarAddedParts, this yields approximately 1 + X + (4 * Y) selects.
> Obviously, this is a lot of selects to happen once you start doing this over
> many Invoices that have many CarInfos with lots of AddedParts. And when my
> numbers average to something like 1000 invoices with about 80 parts a piece,
> this starts getting out of hand quickly.
>
> So, the problem I am facing is I do not know a way to avoid all of these
> selects with OJB. Writing it on my own, I am able to reduce the number down
> to a fixed 4 selects. I could probably widdle it down more, but I would
> prefer some maintainability of the code, which is why I would like to keep
> OJB. Does anyone know of any tricks?
>
> Basically, I think my problem is when you have a M:N:O relationship that needs
> to be looped through. (Also note: in the actual problem I have, I have
> several of these relationships in existance. Think of it as though I had
> seperate cases for Cars and Trucks, with a few differences between them.)
>
> Finally, I thank anyone who had the patience to read through this example and
> question. Hopefully, I made it simple enough to make sense without being
> useless.
>
> Thanks,
>
> -josh
>
>
>
> ---------------------------------------------------------------------
> 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]
