+ Start a Discussion
@altius_rup@altius_rup 

StandardSetController bug - filter clause should be added to existing WHERE clause, not replace it

OK, I have made extensive research on StandardSetController before implementing quite a complex page with features not supported by <apex:listView> or <apex:enhancedList>.

 

I have implemented pagination and column ordering, as well as some client-side multi-line selection and actions.

 

I have implemented dynamic read/write or readonly control line by line, depending on the Approved status of each line.

 

On this path, I have encountered the bug setting page size :

http://boards.developerforce.com/t5/Visualforce-Development/StandardSetController-setPageSize-does-not-work/td-p/202018

or here

http://boards.developerforce.com/t5/Visualforce-Development/bug-StandardSetController-or-QueryLocator-ignores-SOQL-where/td-p/99500

so I do not change page size.

 

 

I have another problem, and think I have the solution.

My StandardSetController is on a Parent/Children page, so filtered (by a where clause) to the id of the parent :

SELECT ID Rep__c STATUS_Done__c, isReadWrite__c, LastModifiedDate
FROM   CE_Line__c
WHERE  Rep__c = :repid

 

When I apply a filter in my VF page like this

<apex:panelGroup id="listViewFilterGrp">
    <apex:outputLabel value="Filter: "/>
    <apex:selectList value="{!CELines.filterID}" size="1">
        <apex:actionSupport event="onchange" action="{!updateMyLines}" rerender="table"/>
        <apex:selectOptions value="{!CELines.ListViewOptions}"/>
    </apex:selectList>
</apex:panelGroup>

and apply a filter, the 'WHERE Rep__c = : cerepid' clause gets wiped out : the returned list is filtered according to the definition of the filter, but across all RepIds !

 

Now I have spotted this person's problem with a WHERE clause being added to their Database query when filtering :

http://boards.developerforce.com/t5/Apex-Code-Development/ERROR-at-Row-1-Column-132-unexpected-token-WHERE/td-p/118177

 

This actually shows that the filter simply removes your own WHERE clause and *replaces* it with the equivalent clause from the filter.  How silly !!!  The filter clause should be *added* to my existing clause with AND, and everything would be fine.

 

Salesforce, please fix this bug to make StandardSetController filtering work on a Parent/Children page.

 

Thanks,

Rup

 

mtbclimbermtbclimber
Thanks for the post Rup. This is not a bug, it is working as designed. The feature was designed to allow a meaningful override to the standard list page. In that use case the filters used should always be those as defined and not further constrained. Preserving the where clause while overlaying user-defined filters is useful and something we looked at but felt we needed to put more design effort toward.
@altius_rup@altius_rup

Andrew,

Thanks for picking up so quickly on my post, great service !

 

Not a bug ?  Well, I will accept that you designed the filters that way ...

But how, then, do you suggest I use StandardSetController and filtering a related list :

In my app, I have a list of Commercial Objectives (1 per customer) related to a Rep record. If I want to filter only Commercial Objectives related to customers in a certain business chain, I need that filter to still include the same Rep parent record.

 

Now, as you can imagine, I can't ask users to always include a clause to filter the Rep as well as the fiscal year in their custom filter; I was expecting my own WHERE clause to be included.

What can I do to work around this problem ?

 

I hope you can help,

Rup

MikenameMikename

I am having a similar issue and seem to be blocked at every turn.

 

1) I have to limit the ssc instantiation result set to 10,000 rows to avoid a query locator governor limit. My WHERE clause within the query locator is stripped out when the filter is applied making my 10,000 far less defined that they could be. This results in nowhere near 10k rows matching both the WHERE clause *and* the List View FilterId coming back. 

 

2) If I try to work around by instantiating the standard set controller with a list of values (as shown as an option at: http://www.salesforce.com/us/developer/docs/pages/Content/apex_pages_standardsetcontroller.htm ), the setFilterID does nothing to the result set, yet does not generate an error. So, now I have my where clause but not my List View Filter

 

3) There is no way that I am aware of to generate the WHERE clause defined in a list view from an apex class. At the moment the only docs that mention being able to describe the contents of a list view apply to the meta-data api ( http://www.salesforce.com/us/developer/docs/api_meta/Content/meta_listview.htm#listViewFilter ), which I cannot get to via apex.

 

This is a pretty big hole that is obviously being solved internally with the generation of the list view WHERE clause to replace the WHERE clause on a ssc queryLocator. Is there any hope for us to gain access to that solution?

 

I'd also be very interested to hear about any other workarounds!

 

Mike

 

GoForceGoGoForceGo

I am running into this.

 

The only workaround I can think of fails when there are more than 10,000K records in a filter view.

 

The workaround would be to use Apex to overlay i.e with your where clause let's say you have 200 records. Without the where clause but with the filter applied,  there are 6000 records from various Reps. You would start with 200 records and see how many of them are in the 6000 just returned. It would be a number < 200.

 

This works fine if you have < 10K records. If you more than 10K records, the SetController returns only 10,000K record. The end result is erratic, for some where clauses you might see all records, others a few, and some zero!


Stumped, since I have more than 10K records.

GoForceGoGoForceGo

The next best thing I could do is to remove any list view filters that have > 10K records. I found getCompleteResult() doesn't work. I had to use getResultsSize and see if it is < 10K.