Page 2 of 3
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 5:28 am
by achiary
Assuming IN operator is supported in databaseManager.addTableFilterParam and so I do not need a previous Select statement, I tried the following :
var success = databaseManager.addTableFilterParam('cotizaciones', 'clientes', 'provincia', 'IN', (1,6), 'listaPcias')
and also
var success = databaseManager.addTableFilterParam('cotizaciones', 'clientes', 'provincia', 'IN', (6,1), 'listaPcias')
in both cases it shows the records that match the last parameter mentioned in the IN list (in the first example it shows records in which provincia is = 6; in teh second, those in wich provincia is = 1).
I did not find anything in the documentation to clarify how to properly write the IN list.
I am using 4.1 beta 4 .
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 7:47 pm
by davidaarong
That didn't work.. too many arguments.
Any other ideas?
-David
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 7:55 pm
by ngervasi
This is how I setup multiple filters using the IN operator, it should give you a good starting point:
- Code: Select all
// Add table filters
var filter = arguments[0]
var _query = 'select partner_filters.id_partner from partner_filters where partner_filters.parent_id = '+ filter;
// Filter the partners table
var _success = databaseManager.addTableFilterParam(controller.getServerName(), 'partners', 'partners_id', 'IN', _query);
if(!_success) return 1;
// Filter the contacts table
var _success = databaseManager.addTableFilterParam(controller.getServerName(), 'contacts', 'id_partner', 'IN', _query);
if(!_success) return 2;
// Filter the loginslog table
var _success = databaseManager.addTableFilterParam(controller.getServerName(), 'login_logs', 'id_partner', 'IN', _query);
if(!_success) return 3;
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 9:13 pm
by achiary
Finally I made it work :
var success = databaseManager.addTableFilterParam('cotizaciones', 'clientes', 'provincia', 'IN', 'select provincia_id from provincias where provincia_id IN (1,6)', 'listaPcias')
When the operator is "IN" , it seems that the value is the result of a query (that can be prepared previously, as Nicola showed, or written as above).
My question is : is there a way not to use a query result as the value argument in the case of operator IN ?
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 9:37 pm
by davidaarong
Thx Achiary, but it's still too many arguments for Servoy 3.5.
And I can't seem to get ngervasi solution to work either.
So to start again: I need to limit records based on a field (jobcode) for a particular user (larivera).
the jobcode i want viewable are: 3440,4958,3465,4959,0000 for user name larivera.
- Code: Select all
if (security.getUserName() == 'larivera'){
var NestedIN = "SELECT emplid FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')";
databaseManager.addTableFilterParam('rcs_billing_sharedcore', null, 'jobcode', 'IN', NestedIN);
I have this in a Global Method that launches at start up
However, no records appear. And yes, there are records in the DB -- it works just fine by running this sql query against the db:
- Code: Select all
SELECT emplid,jobcode FROM core_rso_emp WHERE jobcode IN('3440','4958','3465','4959','0000')
So my question remains: how can i pass addTableFilterParam a list of parameters as opposed to just one in Servoy 3.5?
-David
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 9:46 pm
by rgansevles
davidaarong wrote: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
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 10:04 pm
by achiary
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.
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 10:16 pm
by davidaarong
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
- Code: Select all
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?
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 10:35 pm
by achiary
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);
Re: multiple addTableFilterParam
Posted:
Tue Nov 04, 2008 10:46 pm
by davidaarong
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]:
- Code: Select all
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:
- Code: Select all
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.
Re: multiple addTableFilterParam
Posted:
Wed Nov 05, 2008 12:19 am
by achiary
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.
Re: multiple addTableFilterParam
Posted:
Wed Nov 05, 2008 1:02 am
by davidaarong
hi achiary,
I got it to work -- seems i needed to remove the null for a table name and specifically call a table name
- Code: Select all
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
Re: multiple addTableFilterParam
Posted:
Wed Nov 05, 2008 5:23 am
by achiary
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
Re: multiple addTableFilterParam
Posted:
Thu Nov 06, 2008 8:08 pm
by david
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:
- Code: Select all
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.
Re: multiple addTableFilterParam
Posted:
Fri Nov 07, 2008 5:58 pm
by achiary
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 :
- Code: Select all
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 ?