Records sets

List objects contains DAO objects that are fetched from result set. However it is important to point, that list object does not contain all objects at once - it only has handle to results set from database and creates new instance every time you fetch next object, so you can freely modify filters, criteriums, limits etc. without worry about performance. Lists are mainly mentioned to work as iterators so if you would want to fetch some objects directly you have to use rewind() method first to initialise results cursor and then fetch single objects using current() for fething and next() for moving to next record. Here is example that shows basic usage of list object including parts explained in following paragraphs like limiting, iterators and counting:

1. <?php
2.  
3. // to not repeat all that stuff
4. include('quickstart.php');
5.  
6. // creates new players list object
7. $players = new OTS_Players_List();
8.  
9. // count of all players - Countable interface implemented
10. echo 'There are ' . count($players) . ' players in our database.', "\n";
11.  
12. // sets limitation - 10 rows starting from third (offset 2 means 2 first rows are skipped)
13. $players->setLimit(10);
14. $players->setOffset(2);
15.  
16. // iterates throught selected players
17. foreach($players as $index => $player)
18. {
19.     // each returned item is instance of OTS_Player class
20.     echo (2 + $index) . ': ' . $player->getName(), "\n";
21. }
22.  
23. ?>

Setting limits and offsets

You can limit set of selected rows by setting LIMIT and OFFSET clauses for SQL query. To reduce amount of selected rows use OTS_Base_List::limit(). To skip starting rows use OTS_Base_List::offset(). Note that you can use offset() only with limit() - if you don't use LIMIT clause, OFFSET is not applied! Also remember that rows are counted from 0, so setting OFFSET to 2 will skip 2 first rows starting from third. Another thing is to mention about limiting and skipping at once - if you set LIMIT to a and OFFSET to b, then query will select up to a rows starting from b + 1 - it means that OFFSET does not reduce number of selecting rows, but only moves starting pointer forward. LIMIT 10 OFFSET 2 will select rows from 3 to 12.

Iterator interface

As it was written in previous paragraphs the most natural way of using lists objects is foreach loop. Lists classes implements Iterator interface which allows you to use it like an array in such case.

Countable interface

Another implemented interface is Countable. It means that you can also use count() on list object to get amount of rows selected by this list. Note that it uses COUNT() SQL structure so it is also very optimalised and doesn't need to fetch all rows before counting.

Filtering results

Very powerful (and very advanced) option is list filtering. It filters selected rows to choose only those which you want to. It is highly flexible, but also quite difficult at first way. With OTS_SQLFilter class you can create nearly any possible WHERE clause based on fields, values and operators (sub-queries and SQL functions are not supported). Again - fitering also works directly on SQL layer so it is extremly fast - nothing is done by PHP, it's pure optimalisation of your work.

The most basic use is it's compareField() method. It compares field of record with literal value:

1. <?php
2.  
3. // to not repeat all that stuff
4. include('quickstart.php');
5.  
6. // creates new players list object
7. $players = new OTS_Players_List();
8.  
9. // creates filter
10. $filter = new OTS_SQLFilter();
11.  
12. // sets filter to choose only players with level 8
13. $filter->compareField('level', 8);
14.  
15. // sets filter on list
16. $players->setFilter($filter);
17.  
18. // iterates throught selected players
19. foreach($players as $index => $player)
20. {
21.     echo $player->getName(), "\n";
22. }
23.  
24. ?>

Operators and criteriums

By default using compareField() method you execute simple equal comparsion. But you can of course change operator of relation to other - as third parameter you pass operator which you want to use (= by default). You use operator constants for that:

• OTS_SQLFilter::OPERATOR_EQUAL for = (equal).
• OTS_SQLFilter::OPERATOR_LOWER for < (lower).
• OTS_SQLFilter::OPERATOR_GREATER for > (greater).
• OTS_SQLFilter::OPERATOR_NEQUAL for != (not equal).
• OTS_SQLFilter::OPERATOR_NLOWER for >= (not lower).
• OTS_SQLFilter::OPERATOR_NGREATER for <= (not greater).
• OTS_SQLFilter::OPERATOR_LIKE for LIKE (SQL string wildcard comparsion - matching).
• OTS_SQLFilter::OPERATOR_NLIKE for NOT LIKE (SQL string wildcard comparsion - not matching).

Also by default all appended criteriums are obligatory - using AND to concat. You can of course change that to alternative possible criteriums - using OR to concat. Fourth parameter defines conditions relationship - to use OR you have to pass OTS_SQLFilter::CRITERIUM_OR constant.

Field representations

As you noticed compareField() allows only literal fields comparsion. This method is only a wrapper for default - more complex one: addFilter(). It accepts field identifiers as operands. It means you can compare fields with fields - using OTS_SQLField class (at all you can do simple fields comparsion using compareField() - right operand can be instance of OTS_SQLField). Passing literal value in PHP effects as comparing to string. For example 'foo' will be used directly in SQL query. If you want to compare field values directly you have to create field identifier objects. For example to compare capacity field value with health field:

1. <?php
2.  
3. // to not repeat all that stuff
4. include('quickstart.php');
5.  
6. // creates new players list object
7. $players = new OTS_Players_List();
8.  
9. // creates filter
10. $filter = new OTS_SQLFilter();
11.  
12. // sets filter to choose players with capacity equal to hit points
13. $filter->addFilter( new OTS_SQLField('cap'), new OTS_SQLField('health') );
14.  
15. // another filter
16. $sub = new OTS_SQLFilter();
17.  
18. // only players with level 8 and higher...
19. $sub->compareField('level', 8, OTS_SQLFilter::OPERATOR_NLOWER);
20.  
21. // ... OR magic level 5 and higher
22. $sub->compareField('maglevel', 5, OTS_SQLFilter::OPERATOR_NLOWER, OTS_SQLFilter::CRITERIUM_OR);
23.  
24. // final result is:
25. // "cap" = "health" AND ("level" = 8 OR "maglevel" = 5)
26. $filter->addFilter($sub);
27.  
28. // sets filter on list
29. $players->setFilter($filter);
30.  
31. // iterates throught selected players
32. foreach($players as $index => $player)
33. {
34.     echo $player->getName(), "\n";
35. }
36.  
37. ?>

Also OTS_SQLField class allows you to create name identifiers of different table then listed table - it means you can for example compare fields of foreign keys:

1. <?php
2.  
3. // to not repeat all that stuff
4. include('quickstart.php');
5.  
6. // creates new players list object
7. $players = new OTS_Players_List();
8.  
9. // creates filter
10. $filter = new OTS_SQLFilter();
11.  
12. // selects player whose rank belongs to guild with ID 5
13. $filter->addFilter( new OTS_SQLField('rank_id', 'players'), new OTS_SQLField('id', 'ranks') );
14. $filter->addFilter( new OTS_SQLField('guild_id', 'ranks'), 5);
15.  
16. // sets filter on list
17. $players->setFilter($filter);
18.  
19. // iterates throught selected players
20. foreach($players as $index => $player)
21. {
22.     echo $player->getName(), "\n";
23. }
24.  
25. ?>

Sub-criteriums

As you noticed there is also one, probably the most advanced feature which you can use - sub-filters. It allows you to group OR-concated conditions with AND-concated groups. It is done by passing another OTS_SQLFilter class instance as parameter to addFilter() method.