Sorting and looping

Classic Servoy
1

Hi all,

I have a dumb question about sorts and finds in Servoy, and I have searched the forum first and read McGilly’s book.

Basically, I understand how to get a foundset using a query, I am OK with SQL queries etc but what I can’t work out straight away is the solution to the following:
I want to find, sort and then re-allocate list of client inventory items from the same table. The 3 columns I am interested in are item_ID, client_ID, disposal_date. I want to find all items that have a disposal date (there’s 600 or more records with the table having 100,000+ total records) before a given date (today will do), I want to sort the items by client, then create a disposal order (records in another table) for each client with their items on it.

What I have come up with so far:

  1. Get a getDataSetByQuery, ORDER BY client_ID ASC - easy.
  2. Loop through the foundset to find what I’m after - easy
  3. Create a subset of foundsets - like a relation in a way, that has the records for each separate client to pass to a method (using args client_ID, [array of items])that creates the disposal order - the method to create the disposal orders is easy but how do you ‘SELECT’ from the array? :oops:

I could create a nested loop that goes through the item table and selects the items due for disposal, then passes the foundset array to the method that creates the disposal order, but not every client has items that need disposal, so this seems like an unnecessarily expensive operation.

I suspect it is my ‘FileMaker’ brain that has let me down. This has got to be easy. Clearly I am on a learning curve! I also think I may have gone about it the wrong way. If someone could just point me in the right direction - no code required, just an abstract description of how the pros handle this would be great.

2

Hi,

I’ll give it a try…

Assuming that you have one table with all the relevant info (not via a join of tables) and that this table has more than 100.000 records, I would let SQL do the number crunching work, and make two queries to be executed with the getDateSetByQuery:

  1. Get the client_id’s which have at least one item to dispose, something like (for SQL Anywhere):
    SELECT DISTINCT client_id
    FROM yourTable
    WHERE disposal_date <= ? // with ? being the todays date in your example
    // and maybe some other statements to limit the result
    ORDER BY client_id ASC

This will return all client_id’s having at least one item to dispose.
Note that the actual SQL syntax might differ somewhat depending on the database that you use (and I didn’t check the statement)

  1. Then loop thru this list of client_id’s with a for loop and extract the item_id’s and disposal_date for each item with a query, something like:
    SELECT item_id, disposal_date
    FROM yourTable
    WHERE disposal_date <= ? // with ? being the todays date in your example
    AND client_id = ? // loop thru the client list returned for the first query
    ORDER BY disposal_date

Hope this helps.

Cheers, stefaan

3

If I have a good understanding of your question, I think this could do the job, without the need of nested loops through foundsets in order to get the right data.

  1. select the records like:
SELECT client_id, list(item_id) FROM yourTable WHERE disposal_date <= ? GROUP BY client_id
  1. this can return you a 2 column dataset. Col 1: client_id, col 2: a comma seperated string with all item_id’s
  2. loop through this dataset and pass the columns to the ‘create disposal order’ method.
  3. in the ‘create disposal order’ method you can separate the items by ```
    var $itemArray = arguments[1].split(‘,’)
5) now you can wrap it up by looping through this array in order to create a separate disposal orderline for each item.

Hope this helps!

p.s. The list function has some more possibilities as described here: http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000220.htm
4

Marc and Stefaan,

Thank you very much for your replies, either of your suggestions would do what I require. You’ve both helped my brain shift up a gear! :D

5

Hi Phil

Glad I could help, please do keep in mind that the less queries Servoy needs, the faster it will run.
Even if you need as little data as 1 field for many records, try to get this data within your initial query.

My experience on this was a set of appr. 1700 records where I needed one extra field from a foreign table.
I looped through these record which (in the background) made Servoy query the same field 1700 times.
I added this foreign field to my query and the whole loop (which I still needed for other purposes) was 5 times as fast!