Filtering Arrays

General
1

Hello:

Is there a way built into Xojo to easily filter an Array?

In my example, I have an Array with 4 elements, and I would like to “find” within that Array, those elements that contain the letters “mo” and then add those elements to a Listbox by using a Contains method I dreamed up (see code example)

This is a small list, however there could be thousands of these coming from any type of data source and I’d hate to have to loop through all of them just to filter out (in this example) 2 entries

var itemlist() as String

itemlist = Array(“Zimo”, “Hyperion”, “Conveyor Belt”, “Ammonia”)
itemlist.Sort

For Each FilteredList as String In itemlist.Contains(“mo”)
MyListBox.AddRow(FilteredList)
Next

MyListbox will now contain the items: Ammonia and Zimo

I could perhaps just write an Extension method for the Array and name it, “Contains” or “Find”, however I was hoping that Xojo had some way of doing this without me having to write it.

2

its similar to
For Each item as String In itemlist
ok = item.Contains(searchstring)
ok = MyFilter(item, searchstring)
if ok then MyListBox.AddRow(item)
Next

or sqlite datebase (or in memory db) with sql and where clause

3

Yes. I am currently doing that, but I have to loop through all items in the array just to find which ones meet my search criteria (Contains(“mo”)).

I was hoping something like this were possible?

var arr() as String = Array(some number of items loaded from a remote data source)

var filtered() as String = arr.Contains(“mo”)

As far as the data source, I won’t be able to use sqlite as the data source is a file on a remote server that contains many thousands of items.

4

The source is remote:

As far as the data source, I won’t be able to use sqlite as the data source is a file on a remote server that contains many thousands of items.

but you have to copy it locally in order to work with it:

var arr() as String = Array(some number of items loaded from a remote data source)

So you might as well load it into an in-memory SQLite database so you can use a WHERE field LIKE ‘%searchStr%’ clause. For larger data sets it will be faster that iterating through an array.

5

thanks. i think i’ve figured it out (see my test code). I’ll probably create a Xojo library to reuse this. I’ll clean it up first.

Try // in-memory sqlite db
dbsource = new SQLiteDatabase
dbsource.Connect

dbsource.ExecuteSQL(“CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)”)

for x as integer = 1 to 200000
var nr as New DatabaseRow

nr.Column("name").StringValue = "Name: " + x.ToString
dbsource.AddRow("test", nr)

next

var rs as RowSet

ListBox1.RemoveAllRows()
rs = dbsource.SelectSQL(“select name from test where name like ‘Name: 140%’ order by name asc”)

If rs <> Nil And rs.RowCount <> 0 Then
for each row as DatabaseRow in rs
Listbox1.AddRow(row.Column(“name”).StringValue)
next

rs = dbsource.SelectSQL("select count(name) AS rowcount from test")
Label2.Text = "Rows in db table: " + rs.Column("rowcount").StringValue
Label1.Text = "Filtered Rows: " + ListBox1.Rowcount.ToString

end if

dbsource.close
Catch e As DatabaseException

MessageBox(e.Message)

End Try

6

if it is your server you could import the file into database then you can access a remote database direct.

and something useful

7

It’s not my server

8

One thing is for certain. Both loading an in-memory sql table and loading a Xojo array takes roughly the same amount of time until the number of elements begin exceeding a million.

That’s when you begin to notice just how much faster it is to load a Xojo Array compared to a Table.

In one of my tests, I loaded a Xojo Array with 5 million elements in 2 seconds, while inserting the same 5 million elements into the in-memory sql table took 13 seconds. I’m certain these number will vary based on the system config of the client machine, however I’m reasonably certain the Array will outperform the SQL load no matter the client config.

Granted for my particular use case, I’ll never have to load more than about 40k rows (Array and Table both load in one second).

I also wonder how much more memory the in-memory sql takes up vs the Array?

1 Like
9

Loading might be faster in SQL using and “Insert into Table…” Statement instead of DatabaseRow.
Or maybe even faster with a PreparedStatement.

Although loading takes longer, filtering might be quicker, especially if it has to be done several times.
Also, filtering will be more powerful (read it as advanced filtering features) in SQL than checking an array.

10

have you test this inside of a database transaction? maybe faster

11

Thanks!

Using both a Transaction AND ExecuteSQL to perform an “<>” did reduce the time by nearly half and using SQL does allow for advanced filtering.

12

I bet there’s a faster way to do this. Do you have any sample data and search terms that we can look at?

13

Okay. I’m learning more and more. I decided to create an extension method to illustrate what I was originally asking about.

First, I created the Extension Method:

Signature: Global Function FindAll(Extends values() as String, searchCriteria as String, ByRef err as DatabaseException) As RowSet

// in-memory sqlite db
var rs as RowSet

dbsource = new SQLiteDatabase

Try
  dbsource.Connect
  
  dbsource.ExecuteSQL("CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")
  
  Var stmt As SQLitePreparedStatement = dbsource.Prepare("insert into test (name) values(?)")
  
  dbsource.BeginTransaction
  for each value as String in Values
    stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
    stmt.Bind(0, value)
    stmt.ExecuteSQL(value)
  next
  dbsource.CommitTransaction
  
  stmt = dbsource.Prepare("select name from test where name like ?")
  stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  stmt.Bind(0, searchCriteria)
  
  rs = stmt.SelectSQL
Catch e As DatabaseException
  err = e
End Try

dbsource.close

return rs

Then in a Button_Pressed Event, I added this code:

var myArray() as String

for x as integer = 1 to totalRows
  myArray.Add("Number: " + x.ToString)
next

var err as DatabaseException
var rs as RowSet

rs = myArray.FindAll("%43%", err)

if rs <> Nil And rs.RowCount > 0 then
  ListBox1.RemoveAllRows
  
  for row as Integer = 1 to rs.RowCount
    var itm as String = rs.ColumnAt(0).StringValue
    
    ListBox1.AddRow(itm)
    rs.MoveToNextRow
  next
end if

Everything works fine. Keep in mind that I plan to make this a bit more efficient and clean things up a bit, but ultimately the key statement here is: rs = MyArray.FindAll(SQLSearchCriteria).

Just ignore the ‘err’ parameter for now. I was getting a DB error and I decided to pass it back ByRef so that I could read it.

I could have gone with a Delegate here as well, but I kind of prefer the syntax of the Extension Method.

Having Xojo add a Find and/or FindAll method associated with arrays would be nice so that we can then specify the search criteria and pass a Function Pointer to it which would do the work.

But it’s cool. I’ve learned much going through this motion.

Thanks for all of the feedback.

1 Like
14

I think if you concatenate the wildcard symbols to searchCriteria in FindAll():

stmt.Bind(0, “%” + searchCriteria + “%”)

then the function call would be a little more natural:

rs = myArray.FindAll(“43”, err)

But, the way it is now gives you the flexibility for other types wildcard matching, like begins with or ends with, instead of just contains.

3 Likes