davidaarong:
Thx Achiary, but it’s still too many arguments for Servoy 3.5.
David,
In Servoy 4 a new (optional) parameter has been added to addTableFilterParam.
In 3.5 you have to remove the last (filter name) parameter.
Rob
David,
I assume jobcode is defined in several tables and you want to filter all of them.
So NestedIN result must be a list of jocodes, but in your current status is a list of employees.
If you have a table of jobcodes, you should try something like :
var NestedIN = “SELECT jobcode FROM jobcodes_table WHERE jobcode IN(‘3440’,‘4958’,‘3465’,‘4959’,‘0000’)”;
If you do not have a table of jobcodes, you should create one.
Hope this helps.
Thx for the reply Achiary.
Jobcode is a field in the table named core_rso_emp. I just need to filter this particular table only.
However, when i use the code i have above – Servoy layout is empty. I know there are records there, however nothing is returned.
when i try to modify the code like this
if (security.getUserName() == 'larivera'){
databaseManager.addTableFilterParam('rcs_billing_sharedcore', null, 'jobcode', 'IN', 'SELECT emplid FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')')
}
I get the following error:
Validatioin error in method ‘On_Open’
message: missing ) after argument list
Any ideas?
David,
I insist in my previous post, because I guess the “select emplid” is the cause of your problem :
-
create a jobcode table (namely “jobcodetable” , with columnns jobcode and description (if not already in your solution)
-
try the following :
if (security.getUserName() == ‘larivera’){
var NestedIN = “SELECT jobcode FROM jobcodetable WHERE jobcode IN(‘3440’,‘4958’,‘3465’,‘4959’,‘0000’)”;
databaseManager.addTableFilterParam(‘rcs_billing_sharedcore’, null, ‘jobcode’, ‘IN’, NestedIN);
thx achiary
so if i understand correctly, it’s because I’m selecting the wrong field in my SQL statement?
So if that’s the case, then the following should work, but doesn’t [It does work when i run the SQL statement against the DB outside of servoy though]:
if (security.getUserName() == 'larivera'){
var NestedIN = "SELECT jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')";
databaseManager.addTableFilterParam('rcs_billing_sharedcore', null, 'jobcode', 'IN', NestedIN)
}
I need to just narrow down the records in the table named core_rso_emp where the job code = ‘3440’ or ‘4958’ or ‘3465’ or ‘4959’ or ‘0000’
if i use the following code addTableFilterParam, it works but it’s not what i need:
if (security.getUserName() == 'larivera'){
databaseManager.addTableFilterParam('rcs_billing_sharedcore', null, 'jobcode', '=', '3440')
}
what i need is a way to use addTableFilterParam to filter several jobcodes, not just one.
David,
The problem I see with the following :
if (security.getUserName() == ‘larivera’){
var NestedIN = “SELECT jobcode FROM core_rso_emp WHERE jobcode IN(‘3440’,‘4958’,‘3465’,‘4959’,‘0000’)”;
databaseManager.addTableFilterParam(‘rcs_billing_sharedcore’, null, ‘jobcode’, ‘IN’, NestedIN)
}
is that there may be repeated values of jobcode in core_rso_emp and I do not know which is the behavior of databaseManager.addTableFilterParam in that case.
That is why I insist that you create a jobcode table and do the select on that table, which will assure unique jobcode values, as I mentioned in my last post.
hi achiary,
I got it to work – seems i needed to remove the null for a table name and specifically call a table name
if (security.getUserName() == 'larivera'){
var NestedIN = "SELECT jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')";
databaseManager.addTableFilterParam('rcs_billing_sharedcore', 'core_rso_emp', 'jobcode', 'IN', NestedIN);
}
thx for all your help
Hi David,
I am glad it worked , we all learned a lot. This is a feature I need to use soon so it has been very usefull for me. And it is really very powerfull !
I have read the Manuals and there is also a “addFoundsetFilter” function in which the IN operator can be used for multiple selections.
Regards
Any notes/comments/testing results on speed when filtering with an IN subquery?
In a SaaS setting or where the outer table is large, I would expect having an IN subquery tacked onto every query Servoy generates could be a real performance hit.
With MySQL, IN subqueries can be much slower than the equivalent IN(value_list) operator that lists the same values that the subquery would return.
If the values of your subquery won’t change during a session, I would get those values once on startup and use them as values for the IN operator of the filter:
var NestedIN = "SELECT jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')"
var values = databaseManager.getDataSetByQuery(controller.getServerName(), NestedIN, null, 10000000)
valuesArray = values.getColumnAsArray(1)
var valueList = "("
for (var i = 0; i < valueArray.length; i++) {
valueList += valueArray[i] + ", "
}
valueList += ")"
databaseManager.addTableFilterParam('rcs_billing_sharedcore', 'core_rso_emp', 'jobcode', 'IN', valueList)
If the subquery result is variable and you have large datasets, I would start thinking along the lines of using multiple database connections (example: each client get their own database) to split up the data.
Nested IN subqueries can be done and will work fine on small datasets – but I would be scared that the lack of optimization it introduces would come back to bite me in the *** at some future point.
David,
Good observation about performance, I will take it into account.
Coming back to the way to do it, can I use a list in the filter without doing a query ? I suposse so, but I could not do it yet.
I do the following :
valueList = '(1, 6)'
application.output(valueList )
databaseManager.addTableFilterParam( 'cotizaciones', 'clientes', 'provincia', 'IN', valueList)
where provincia is an integer type, but it shows nothing (filters all records).
What am I doing wrong ?
Sorry to mislead you, my code has an error in it. I wrote it by hand and had SQL query format in my head.
The upshot is that the value parameter of the addTableFilterParam either needs to be one value (string or number) or an array if passing multiple values. So my code above corrected:
var NestedIN = "SELECT jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')"
var values = databaseManager.getDataSetByQuery(controller.getServerName(), NestedIN, null, 10000000)
valuesArray = values.getColumnAsArray(1)
databaseManager.addTableFilterParam('rcs_billing_sharedcore', 'core_rso_emp', 'jobcode', 'IN', valuesArray)
Hey David, maybe you were typing in a hurry but beware of this:
var values = databaseManager.getDataSetByQuery(controller.getServerName(), NestedIN, null, 10000000)
Always use -1 unless you are 100% sure that the query will give you a predefined amount of records back, I know it looks stupid but if in the future your query will return more than 10000000 records you’ll be in deep trouble and debugging things like this can be a real nightmare.
So this is the correct code:
var values = databaseManager.getDataSetByQuery(controller.getServerName(), NestedIN, null, -1)
Sorry to be so picky but it happened to a colleague of mine and we had our share of pain and lost one day to figure out what was wrong.
I guess that would make for a tough debugging job Great reminder.
Thanks David, for clarifying the format of ‘value’ as an array when the operator is ‘IN’, it is not in the manuals.
So I could make it work also in the following way :
var arrayprov = new Array(1,6,15);
application.output(arrayprov )
databaseManager.addTableFilterParam( 'cotizaciones', 'clientes', 'provincia', 'IN', arrayprov)
So, when the IN values are already known (in my case provincias 1,6 and 15) I can fill an array and I do not need to perform a query.
I guess the query must be used when the result is unknown and depends on a certain condition.
Hi,
Any news on using relations in the addFilterParam() ?
Using pk_field in (select …) performs (still) bad (no 200 records limit in subquery).
Or any other solution ?
Regards,