SearchPanes ignoring Table option when using Server Side with ReadTable Option in Editor

SearchPanes ignoring Table option when using Server Side with ReadTable Option in Editor

ITAppData@HomeStreet.comITAppData@HomeStreet.com Posts: 31Questions: 6Answers: 1
edited September 2020 in SearchPanes

Link to test case: Unsure how to recreate, data is sensitive so can't put it out there and
Debugger code (debug.datatables.net):
Error messages shown: Query is incorrect, causing filters to show incorrect values and totals and not filtering as expected
Description of problem: I have a .NET Editor setup that is using the ReadTable function to read data from a SQL view for a base table. Recently, I had to switch this to Server Side, as the number of records is creeping towards 100,000 and load times were getting pretty rough. I was trying to implement server side SearchPanes, but the queries seem to be ignoring the "Table" option and simply reading from the base table, which is of course not working properly.

I have the following code for the backend:

Editor is setup as follows:

var editor = new Editor(_db, "NCOA_ReturnedRecords", new[] { "Loan_Number", "LoadedByJob_Id" }).ReadTable("v_NCOAGoodRecordsForReview").Model<v_NCOAGoodRecordsForReview>();

The field I am wanting the SearchPane on is:

                        editor.Field(new Field("Approval_Status").Options(new Options().Table("Approval_Status").Value("Approval_Status").Label("ApprovalDesc")).
                            SearchPaneOptions(new SearchPaneOptions().Table("v_NCOAGoodRecordsForReview").Value("Approval_Status")
                            .Where(w => { w.Where("Loan_Number", "(SELECT Loan_Number FROM dbo.v_NCOAGoodRecordsWithAddressChanges)", "NOT IN", false); })
                        ));

However, when viewing the Network call it is returning the following:

SELECT DISTINCT  [Approval_Status] as 'label', [Approval_Status] as 'value', COUNT(*) as 'total' FROM  [NCOA_ReturnedRecords] WHERE ([Loan_Number] NOT IN (SELECT Loan_Number FROM dbo.v_NCOAGoodRecordsWithAddressChanges) ) GROUP BY Approval_Status

You notice the FROM is pulling from the Editor base table, not the specified table on the SearchPaneOptions, however the WHERE query is getting added appropriately.

I have another table that simply uses the view without need for a ReadTable (because no editing is allowed anyways) and this works as expected pulling from the proper table.

I can provide some additional information if necessary, but unsure if I'm doing something incorrectly on this as I wouldn't expect the query it is attempting.

Thanks :)

Replies

This discussion has been closed.