Datasource Paging Issue (Revised Again)

Question

See Datasource Paging Issue (Revised) for the original question.

Markus, you were kind enough to help with out with the issue of incorporating a record count into a query using a calculated datasource. I have a search form with 15 widgets - a mix of date ranges, dropdowns, text values and ._contains, ._equals, ._greaterThanOrEquals, ._lessThanOrEquals, etc.

I have tested this extensively against mySQL SQL code and it works fine.

I have now added a 16th parameter PropertyNames, which is a list with binding @datasource.query.filters.Property.PropertyName._in and Options blank. The widget on the form is hidden because it is only used for additional filtering.

Logic such as the following is used, such that a particular logged-in user can only view their own properties. So if they perform a search and the Property is not specified we do:-

if (params.param_Property === null && canViewAllRecords === false) {
    console.log(params.param_PropertyNames); // correct output
    ds.filters.Property.PropertyName._in  = params.param_PropertyNames;
}

The record count (records.length) is correct, and if I for loop through the array of records the record set is correct.

However, on the results page the table displays a larger resultset which omits the PropertyNames filter. So if I was to search on Status 'Open' (mySQL results 50) and then I add a single value ['Property Name London SW45'] for params.param_PropertyNames the record count is 6, the records array is 6 but the datasource display is 50. So the datasource is not filtering on the property array.

Initially I tried without adding the additional parameter and form widget and just using code such as

if (params.param_Property === null && canViewAllRecords === false) {
    console.log(params.param_PropertyNames); // correct output
    ds.filters.Property.PropertyName._in  = properties; // an array of 
        properties to filter out
}

But this didn't work, hence the idea of adding a form widget and an additional parameter to the calculated recordcount datasource.

If I inspect at query.parameters then I see:-

"param_Status": "Open", 
"param_PropertyNames": ["Property Name London SW45"],

If I inspect query.filters:-

name=param_Status, value=Open
name=param_PropertyNames, value=[]}]}

It looks as though the filter isn't set. Even hard coding ds.filters.Property.PropertyName._in = ['Property Name London SW45'], I get the same reuslt.

Have you got any idea what would be causing this issue and what I can do for a workaround ?

Your param_Property, what is the type? Is it string, number, list{string}, list{number}, etc.? If that parameter is a list type parameter then I have found doing a logical evaluation against null doesn't work a lot of the time because the default for a list type parameter appears to be [], i.e. an empty array, instead of null. So your logical evaluation might need to be changed to param_Property.length === 0 or param_Property.length > 0. — Markus Malessa
params..param_Property is string, and params.param_PropertyNames is a string list, so I'm basically saying, SELECT * FROM Incident.Property where Incident.Property_fk = Property.Id and Property.PropertyName IN ('Property1 London SW1', Property2 London SW1'); — SilverFox
Plus the logic is fine because the console.log statement is executed, so definitely not a null issue. It is the case that the filter for the new parameter is being executed because as I said, the record count and array of returned records is fine, but when you look at results in a bound table then the table has more records than it should and reflects the fact that the last filter condition isn't present. — SilverFox
I think your problem lies in how param_PropertyNames is transferred between datasources or how it is not transferred between datasources. The first set of code appears to be hard coded in your calculated datasource (and returns count only) and the second set of code appears to be hard coded in your datasource that is supposed to return the actual records to the client, however neither is clear if that code is on the client or on the server, I would try to edit the question to make that clear. Do the same for code snippets 3 and 4, are they client or server. — Markus Malessa
Markus, the solution was simple. If I have a dropdown and a corrresponding filter then if I select something from the dropdown sure enough it gets passed. I was trying to set the value of the array for the ._in comparison on the server so no wondwr it would not work. If I hide the dropdown - because it used for additional filtering that you don't want the user to see - and do something like ... widget.root.descendants.ddPropertyNames.value = ['Property1','Property2','Property3']; ... then it works as intended. — SilverFox

Markus Malessa Markus Malessa · Accepted Answer · 2019-06-18T13:44:54

Using a server side solution I would suggest editing both your SQL datasource query script (server side) that is supposed to filter by this property list and including the same code in your server side script for your calculated Count datasource. The code would look something like this, not knowing your exact details:

var subquery = app.models.Directory.newQuery();
subquery.filters.PrimaryEmail._equals = Session.getActiveUser().getEmail();
subquery.prefetch.Property._add();
var results = subquery.run();

if(!results[0].CanViewAllRecords) {
  query.filters.Property.PropertyName._in = results[0].Property.map(function(i) {return i.PropertyName;});
}

By adding this code you are filtering your directory by your current user and prefetching the Property relation table, then you set the filter only if your user canviewallRecords is false and use JS map function to create an array of the PropertyName field in the Property table. As I stated, your code may not be exactly the same depending on how you have to retrieve your user canviewallrecords property and then of course I don't know your relation between user and Property table either, is it one-to-many or other. But this should give you an idea how to implement this on server side.

Datasource Paging Issue (Revised Again)

1 Answers