0

I have two objects:

OBJECTA ID Description Cost Price SKU

OBJECTB ID Qty SKU

Essentially, the SQL version of my query would be:

SELECT A.Description, A.Cost, A.Price, A.SKU, SUM(B.Qty) 
FROM A JOIN B ON A.SKU = B.SKU
GROUP BY A.Description, A.Cost, A.Price, A.SKU

However, in order to accomplish this in SOQL, I grabbed all of object A and then looped through in a for loop and updated it with the sum from object b.

Public class wrapper {
  string Description;
  decimal Cost;
  decimal Price;
  string SKU;
  integer Qty;
}

Public List<ObjectA> objAs = [SELECT Description, Cost, Price, SKU FROM ObjectA]
for(ObjectA oa : objAs) {
 wrapper w = new wrapper();
 w.Description = oa.Description
 w.Cost = oa.Cost;
 w.Price = oa.Price;
 w.SKU = oa.SKU;

 AggregateResult [] rs = [SELECT SUM(Qty) FROM ObjectB WHERE B.SKU = : oa.SKU]
 if(rs != null) {
   decimal temp = (Decimal)ar.get('myqty');
   w.Qty = temp.intValue();
 }

The wrapper is then added to a list of wrappers and displayed. All works well in the Sandbox, but in production, I run into too may SOQL queries 101. This is because I query for the sum of the qty for each, individual object A. I can see how I can get a list of SUM(Qty) and SKU for all the SKUs from Object B in one query. I just can't sort out how I can then look it up by SKU to "apply" it to the proper Object A.

Hope that all makes sense. Please point me in the proper direction!

0

Your field/object naming is a little puzzling, but this outline illustrates a general approach to the problem where a single aggregate query is done grouped by the set of SKU values with a map used to ensure the values can be lookup up efficiently:

...
Wrapper[] wrappers = new Wrapper[] {};
Set<String> skus = new Set<String>();
for(ObjectA oa : objAs) {
    skus.add(oa.SKU);
    Wrapper w = new Wrapper();
    ...
    wrappers.add(w);
}
Map<String, Integer> skuToQty = new Map<String, Integer>();
for (AggregateResult ar : [
        select SKU s, sum(Qty) q
        from ObjectB
        where SKU in :skus
        group by SKU
        ]) {
    skuToQty.put((String) ar.get('s'), (Integer) ar.get('q'));
}
for (Wrapper w : wrappers) {
    Integer qty = skuToQty.get(w.SKU);
    w.Qty = qty != null ? qty : 0;
}
  • Keith--thanks that was exactly what I needed to understand :) Thanks for taking the time to line it out. – KMT333 Jun 13 '14 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.