multiple addTableFilterParam

Questions, tips and tricks and techniques for scripting in Servoy

Re: multiple addTableFilterParam

Postby achiary » Tue Nov 04, 2008 5:28 am

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 .
achiary
 
Posts: 83
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: multiple addTableFilterParam

Postby davidaarong » Tue Nov 04, 2008 7:47 pm

That didn't work.. too many arguments.

Any other ideas?

-David
davidaarong
 
Posts: 70
Joined: Wed Jan 10, 2007 12:43 am

Re: multiple addTableFilterParam

Postby ngervasi » Tue Nov 04, 2008 7:55 pm

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;
Nicola Gervasi
sintpro.com
SAN Partner
ngervasi
 
Posts: 1485
Joined: Tue Dec 21, 2004 12:47 pm
Location: Arezzo, Italy

Re: multiple addTableFilterParam

Postby achiary » Tue Nov 04, 2008 9:13 pm

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 ?
achiary
 
Posts: 83
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: multiple addTableFilterParam

Postby davidaarong » Tue Nov 04, 2008 9:37 pm

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
davidaarong
 
Posts: 70
Joined: Wed Jan 10, 2007 12:43 am

Re: multiple addTableFilterParam

Postby rgansevles » Tue Nov 04, 2008 9:46 pm

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: multiple addTableFilterParam

Postby achiary » Tue Nov 04, 2008 10:04 pm

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.
achiary
 
Posts: 83
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: multiple addTableFilterParam

Postby davidaarong » Tue Nov 04, 2008 10:16 pm

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?
davidaarong
 
Posts: 70
Joined: Wed Jan 10, 2007 12:43 am

Re: multiple addTableFilterParam

Postby achiary » Tue Nov 04, 2008 10:35 pm

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);
achiary
 
Posts: 83
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: multiple addTableFilterParam

Postby davidaarong » Tue Nov 04, 2008 10:46 pm

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.
davidaarong
 
Posts: 70
Joined: Wed Jan 10, 2007 12:43 am

Re: multiple addTableFilterParam

Postby achiary » Wed Nov 05, 2008 12:19 am

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.
achiary
 
Posts: 83
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: multiple addTableFilterParam

Postby davidaarong » Wed Nov 05, 2008 1:02 am

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
davidaarong
 
Posts: 70
Joined: Wed Jan 10, 2007 12:43 am

Re: multiple addTableFilterParam

Postby achiary » Wed Nov 05, 2008 5:23 am

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
achiary
 
Posts: 83
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

Re: multiple addTableFilterParam

Postby david » Thu Nov 06, 2008 8:08 pm

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.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: multiple addTableFilterParam

Postby achiary » Fri Nov 07, 2008 5:58 pm

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 ?
achiary
 
Posts: 83
Joined: Wed Nov 14, 2007 3:29 pm
Location: Argentina

PreviousNext

Return to Methods

Who is online

Users browsing this forum: No registered users and 11 guests