foundSet.loadRecords()

Classic Servoy
1

Hi ,

How can I apply (in an easy way) a filter to a foundset on top of (logical and) the existing foundset filter through foundset.loadRecords(SQL-query, args) ?
(I cannot use foundset.search())

Regards,

2

Excellent question!

Option #1 (one of my favorite tricks)
Keep in mind that although databaseManager.getDataSetByQuery does NOT honor existing foundset filters, using foundset.loadRecords(dataSet) DOES honor the filters. It will filter out the records that are not valid for the existing filter. (I’m assuming here that you are using real foundset filters, like foundset.addFoundSetFilterParam(…))
So, you can do something like
var ds = databaseManager.getDataSetByQuery(…) //gets everything
foundset.loadRecords(ds) //will filter out based on founsetsetfilterparams

You can get more advanced with this and use databaseManager.getFoundSet(…) to get a generic foundset not tied to a form, and then add your FoundSetFilterParam values, and then load sql into that, and use the generic foundset as your way of filtering.

Option #2

  • Use DatabaseManager.getSQL(foundset) to get the sql used to generate the current foundset
  • Use DatabaseManager.getSQLParameters(foundset) to get the arguments used in the sql query
  • Next make your own query that wraps the previous query using the IN statement.

var curSQL = databaseManager.getSQL(foundset)
var myCustomSQL = “SELECT * FROM table WHERE blah=‘something’ AND tablepk IN (curSQL)”

I don’t fully remember, but you may have to modify curSQL so that it only Selects the PK.

Option #3
When you add whatever your existing foundset filter params are, keep track of them somewhere. Then when you write your custom SQL, you can concatenate and parse to build the full SQL statement will all required filters.

3

For Option #3:
Servoy is already keeping track of the filter params (and so it should, otherwise how would it apply them?), so you don’t even need to keep them yourself…

Use JSFoundset.getFoundSetFilterParams([filterName]) to get a list of previously defined foundset filters.

4

Thanks for the answers, guys !

I was thinking about option #2 because it takes all the foundset SQL (and not only the existing filters added through addFoundSetFilterParam).

Wouldn’t a new 3rd parameter for loadRecords(_sql, _sql_params, _preserve_existing_foundset_sql) be nice ?

Then Servoy would do that work for us (a good programmer is lazy a IT-teacher told me once ! :lol: )

Thanks again,

5

Lambert,

You can also add a foundset filter and remove it later:

if (foundset.addFoundSetFilterParam('mydp', 'in', 'select x from y', 'custFilter'))
{
	foundset.loadAllRecords();//to make param(s) effective
	....
	foundset.removeFoundSetFilterParam('custFilter')
	foundset.loadAllRecords();//to make param(s) effective
}

Rob

6

Hi Rob,

I was talking about filtering through foundset.find() and foundset.search()…

There is not that much difference between a filter (a subset of records) through foundset.search() or through foundset.addFoundSetFilterParam() in terms of user experience.

The DatabaseManager.getSQL(foundset) gives it all to us…

Thanks.

7

if you want to add filters on top of an existing foundset,
cant you then just you the find()/search (with reduce foundset boolean to true) so that you just add another query criteria?

8

Yes, I know I can but I want the same functionality for foundset.loadRecords(_sql, _params)…

Why was foundset.loadRecords(_sql, _params) invented by Servoy while foundset.search() was already there ?

We keep the SQL as much ansi as possible and we find it very powerful in the filtering of foundsets !

Thanks for the help.

9

because with the sql variant you can set the whole sql you want at once.
And to use that method to add a part of sql to the existing sql is strange, its not meant for that it is to set the complete state.
Else where do we need to insert the new sql in the sql we already have? Before order by? in the where part? But with an or OR or an AND?
We should try to get rid of plain text sql, thats where we will be heading. (by introducing some QueryObject that represents a sql statement)

10

Hi Johan,

Glad to hear that. I am not too happy about hard coding SQL my self so what you say (QueryObject) sounds very promising !

We now use in our app an end user data dictionary driven filter condition builder which delivers plain SQL with and, or and parenthesis. That SQL we throw in the foundset via foundset.loadRecords(). Maybe in the future we can use that new QueryObject for filter on top of the existing foundset SQL/filter.

Thanks.

11

We have a similar requirement where our users use normal find mode to perform some filtering on a foundset but then may request additional filtering based on a SQL query (in our case the reason can’t use the normal foundset.find()/search() calls is because we need to perform a spatial query which isn’t supported directly by Servoy). So far we haven’t found a solution for this. As noted by the original poster foundSet.loadRecords() doesn’t have an option to allow applying a query on top of existing conditions. We can’t use dataManager.getSQL()/getSQLParameters() and build a completely new query because this isn’t always reliable (case #243603).

Has anyone found a solution for this? I don’t think the QueryObject possibility mentioned by Johan will address our use case.

Thanks,
Corey

12

Corey,

Can’t you use the approach I suggested above?

About the query object, that is actually the fix for databaseManager.getSQL() not always being reliable, the query object does not contain references to temp tables.

Rob

13

Rob,

I didn’t read your sample code closely enough. The trick you’re describing is to add a filter to the existing foundset with our custom spatial query, apply the filter (via loadAllRecords()) and then remove it again right away and then even though the filter is removed the foundset will still reflect the temporary filtering even after removing the filter? Or am I misunderstanding? If I’m understanding correctly then this may work. The one big caveat with this solution is that it doesn’t appear to be possible to specify an array of parameters to be applied to the SQL statement (since our SQL takes a couple of parameters entered by the user) so we’d be opening ourselves up to SQL injection attacks.

Edit: I tried your suggestion and it doesn’t seem to work at all as I understood it from your code snippet. Applying a foundset filter and then calling foundset.loadAllRecords() appears to clear the foundset of any previous records. Additionally, a filter couldn’t be used to extend an existing foundset anyways (could only be used to reduce the found records) which doesn’t fit our use case. Could you explain further how we could use the functionality you described?

Thanks.