SELECT...WHERE...IN - using array values

Classic Servoy
1

I am trying to build an sql query that works as a reduce foundset option (the query spans multiple tables and I cannot use the Servoy reduce find option). As far as I can work out, I need a series of values in inverted commas as match values when I run the new query eg. ```
var query = 'SELECT contactsid FROM contacts WHERE contactsid IN (‘1’, ‘6’, ‘8’)

var set = databaseManager.getFoundSetDataProviderAsArray(foundset, ‘contactsid’);
var query = 'SELECT contactsid FROM contacts WHERE contactsid IN (set);


as it returns an sql syntax error.

How can I convert an array (or a dataset) into the necessary format e.g. '1','2','3', etc to work as the match values for the 'IN' clause? The only saolution I have come up with so far is to loop through the array or dataset adding the commas as text:

var a = databaseManager.getFoundSetDataProviderAsArray(foundset, ‘contactsid’);//convert to array
var b = ‘’;
var c = a.length;
for ( var i = 0 ; i < a.length; i++ )//loop through array, adding surrounding inverted commas to values
{

b += "'"+a[i]+"'";
if (i != c-1)
{
	b += ',';//add a comma for each line except the last
}

}


- is there a better option?
2

Hi David,

Need a bit more info relating to what tables you need to search on here to fully understand the search issue.

In the interim, however, I would say that SQL subquery would solve the problem for you.

Subqueries allow you to run an inner query which returns your first query foundset and then can convert this returned set into a comma delimited format to be used within the outer query which is then running the ‘IN’ clause.

Describe what you want to get and from where and I may be able to help a bit more with the syntax.

Cheers
Harry

3

Harry

Thanks for the suggestion - it looks promising but..

both my select statements (i.e. the one to get the set of values for the ‘IN’ test and the one to get the set of records to match against those values) require multiple unions and I am not sure how to patch the whole thing together.

This works fine: “select contactsid from contacts where last_name = ‘smith’ and contactsid in ()”

…but if the first statement also has multiple unions then it fails - do I have to append the sub-query to each of the unioned statements in the main select?[/code]

4

Hi David,

I am no SQL guru but I know that the general format for the subquery runs as follows and have used it successfully in testing during development:

SELECT clientid
FROM order
WHERE orderid IN
    (SELECT orderid
     FROM orderItem
     WHERE productid = '1000')

So within the bracket following the IN clause is the inner query and the rest is the outer query
I know that SQL runs the inner query first which is this:

SELECT orderid FROM orderItem WHERE productid = '1000'

If that subquery returns two orderid’s of, say ‘1999’ & ‘2000’ then these two values are passed to the WHERE clause of the outer query comma delimited as required by the IN operator so that query now becomes:

SELECT clientid FROM order WHERE orderid IN (1999 , 2000)

As far as I am aware, you can add your subquery statements at any point wherever a column or literal is legal

Thus it would be perfectly logical that you would use the subquery in each Union Statement.

The subqueries would then be run first to determine that criteria passed back to finally be run under the main Union statements !

Good grief, that sounds complex to write never mind read !

I have found that a good (general) SQL resource is the on line MySQL manual at:
http://dev.mysql.com/doc/mysql/en/Scala … eries.html

So this may help you make sense of the gibberish which I have posted !

Harry
[/quote]

5

Actually the gibberish makes perfect sense!

Have tried this and it does work, even with multiple unions both in the sub and main queries. :D

However, the other point I was trying to get across in my first post is not covered by this solution: if I have a current foundset and want to filter this set down further (again having to use sql with unions) how do I proceed. In other words I need to get the pks of the current foundset into a comma delimited list to use within an ‘in’ clause of a new sql query - is there any quick way to do this that you know of? Does this make sense?

6

best way to do it:

var set = databaseManager.getFoundSetDataProviderAsArray(foundset, 'contactsid'); 
var query = 'SELECT contactsid FROM contacts WHERE contactsid IN (';

for(var i=0;i<set.length;k++)
{
    query += '?';
    if(query.length-1 != i)
    {
       query += ',';
    }
}

quer += ')';
controller.loadData(query,set);
7

Johan - tried this in a test solution:

var set = databaseManager.getFoundSetDataProviderAsArray(foundset, 'mainid'); 
var query = 'SELECT mainid FROM main WHERE mainid IN ('; 

for(var i=0;i<set.length;i++) 
{ 
  query += '?'; 
  if(query.length-1 != i) 
  { 
   query += ','; 
  } 
} 

query += ')'; 
controller.loadData(query,set);

but get an error ‘loadData is not a function’ - am I being stupid or am I missing something?

8

OK, I was being stupid :oops: Now realise your loadData was short hand for the whole get dataset and load/show routine!

Assume also that there were a few typos in your example, as this works fine:

var set = databaseManager.getFoundSetDataProviderAsArray(foundset, 'mainid'); 
var query = 'SELECT mainid FROM main WHERE mainid IN ('; 

for(var i=0;i<set.length;i++) 
{ 
  query += '?';
  if(set.length-1 != i) 
  { 
   query += ','; 
  } 
} 

query += ')'; 
var maxReturedRows = 10000;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, set, maxReturedRows);

However, this still takes time to process for larger numbers of records - is there a chance that a future version could add some options to the ‘databaseManager.getFoundSetDataProviderAsArray(foundset, ‘mainid’)’ routine that would return the array already comma delimited, or even (if text values were required rather than pks) comma delimited with inverted commas round the text elements to give a list of match values such as ‘smith’, ‘jones’, ’ peters’ … This would mean a single operation to get the list instead of getting the list and then processing it as above. I don’t know if this is even possible, but it would be useful.

Thanks for the help

9

sorry controller.loadRecords should be used.

10

an array is an array. Wat you want is a concatting that you do now in that loop. And be aware a in query is not unlimmited!!!
Also a query string length is not unlimmited!

11

OK - just wondering! :? I’ll stick to the loop method - it works fine.