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!

1 Answer 1

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;
}
1
  • Keith--thanks that was exactly what I needed to understand :) Thanks for taking the time to line it out.
    – KMT333
    Commented Jun 13, 2014 at 16:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .