function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
rscottrscott 

Limit SOQL results per Item in an 'IN' clause

Requirement: Get the last 10 orders/quotes/etc. for each part on a new order.

ex. select Id from Order__c

where part='part1'

order by createddate desc

limit 10

 

The problem is if we make requests from 3 objects (orders, quotes, and item history, say) and we have more than 33 parts on the order, we hit the governor limit of 100 SOQL queries.

 

I would like to make one call to each object using an 'in' clause with a list of parts. However, I'm not sure how to get the last 10 for each part other than looping through the results (possibly quite large) until I get them. 

 

Any way to limit results for each item in a single query? Alternate ideas?

ThomasTTThomasTT

If the total number of records which could be hit by the "1" query, is less than 10,000 records, and any other query is not executed (inculidng in triggers triggered by the operation), there is one thing you can do.

 

As you said, use "IN" clause to query all possible records, and "ORDER BY" is createddate desc (or part, createdate desc)

If you use for(Order__c o : [select ... where part in :partSet]){} loop, you can read up to 10,000 records so...

 

1. Define Map<String, List<Order__c>> odrMap

2. in the loop, put the Order__c in the odrMap if the List doesn't have 10 Order__c yet.

 

The point is, even though you have 10 parts, and you need only each 10 orders for each, you need to query all Order__c which has those part. So, I strongly recommend:

 

3. Add another condition like "year" or something and put it in the where clause like where creatdate > :startDate and createdate <= :endDate.

4. If the read count (Limist.getQueryRows()) hits 10,000 (Limist.getLimitQueryRows()), show error to make user set proper filter.

 

ThomasTT

rscottrscott
Thanks, Thomas.
 The issue with limiting by a date range is that some parts are more frequently ordered than others. For example, PartA was ordered 200 times in the past six months, while PartB was only ordered 3 times. To get the last 10 orders for PartB we have to go back maybe 2 years which brings back 800 records for PartA. Get a few more parts like PartA and we hit the 10k limit.
 

ThomasTT wrote: 
The point is, even though you have 10 parts, and you need only each 10 orders for each, you need to query all Order__c which has those part.  

This is what I suspected we would need to do. I was just looking for an alternative that would make hitting the 10k record limit less likely.

 

Message Edited by rscott on 10-27-2009 10:51 AM
ThomasTTThomasTT
Well, if "part" is a lookup field, you can use parent-child query and you can use "limit 10" for child query. If not... Make it a lookup field. I've just realized it colud be already lookup field (it can't be just a text field...) - ThomasTT