<<css mode="next" class="sidebar"></css>> (((
- '''<a href="/docs/fRecordSet">Class Documentation</a>''' - <a href="/api/fRecordSet">API Reference</a> - <a href="https://github.com/flourishlib/flourish-classes/blob/master/fRecordSet.php" target="_blank">Source Code</a>
<<toc></toc>>
- fActiveRecord - '''fRecordSet'''
- fORM - fORMColumn - fORMDatabase - fORMDate - fORMFile - fORMJSON - fORMMoney - fORMOrdering - fORMRelated - fORMSchema - fORMValidation
)))
The fRecordSet class provides functionality to load sets of fActiveRecord objects from the database and manipulate them.
Since the class contains quite a number of features and handles finding data even with complex database schemas, having an example database schema to reference is important. The following database tables will be used as the basis for the examples on this page:
Record sets can be created from simple condition arrays, SQL statements, or from an fActiveRecord class. Under the majority of circumstances, the shorthand condition array method provides for an efficient and expressive method to build a record set.
The static method ::build() accepts between one and five parameters to build a record set. The only required parameter is the first, `$class`, which specified the type of record to build. Passing on this parameter will cause all records of that type to be created in the set.
The second parameter is the array of `$where_conditions`. This parameter accepts an array that contains columns and operaters as the key and the value(s) to match as the value. A simple example of matching all users with a status of `Active` is shown below:
Each `key => value` pair in the conditions array represents a single expression that will be joined by `AND` logic to create there `WHERE` clause. It is also possible, with slightly different syntax, to create simple `OR` conditions and use aggregate functions in the `HAVING` clause.
The column name can be any column in the record being created, or can be columns in related records. The following example would select users in a group with the name `Administrators`:
It is also possible to specify columns in a table that is twice-removed from the record being created. The following example would create the groups that contain users who have a favorite including `http://example.com`:
When there is more than one relationship between two tables, the relationship route can be specified by putting it in `{}` after the table name. For example, the following SQL would select all users who are the owner of a resource called `money`:
All of the examples in the column specification section above use a simple equation operator, `=`. The following is a list of all supported operators:
- `=` - equal - `!` - not equal - `<` - less than - `<=` - less than or equal to - `>` - greater than - `>=` - greater than or equal to - `~` - `LIKE '%value%'` match - `!~` - `NOT LIKE '%value%'` match - `^~` - `LIKE 'value%'` match - `$~' - `LIKE '%value'` match
All seven operators work with any single value, and will properly handle the SQL variations needed—such as `=` becoming `IS` for `NULL` values. Below are some examples:
The `=`, `!`, `~` and `!~` operators also support comparison with an array of values. The following example will find all users with the first name `James` or `John`:
This example will return all users with a name like `Joe` or `Fred`:
It is also possible to do `~` matching over multiple columns. If a single string value is set for such a condition, it will be parsed for individual words and quoted phrases. In addition, stop words (such as "the" and "a") will be removed from the parsed words and punctuation will be stripped from the beginning and end of each word parsed.
The following example would find any users that contained `john`, `smith` and `west peabody` in any combination of the `first_name`, `last_name`, `address` and `city` columns:
If another method of parsing search terms is required, it is also possible to specify the value of the conditions as an array of string to match. In this case no further parsing will be done.
There are two other operators available that work with multiple values, the `&~` (AND LIKE) and `><` (intersection) operators.
`&~` accepts two or more `LIKE` patterns and requires that each values match every pattern. The example below would require that the person’s email include both `example.com` and `john`:
The intersection operator works with ranges of values, so it is really only applicable to date/time and numeric fields. It requires two columns in the database table and two values to compare with. If the range of values in the two database columns in any way intersects the range between the two values specified, a match will be made.
For example, if there is an events table in the database that has a start and end date, it is possible to find any single or multi-day events that will occur during the next week with the following code:
While adding `key => value` pairs to the conditions array always joins the conditions using `AND` logic, it is possible to create simple `OR` conditions too. If the array key is two or more column specifications with operators, separated by `|`, and the value is an array of values equal in size to the number of column specifications, an `OR` condition will be created.
The following example will return all users with the first name `John` or that were created after January 1st, 2008:
These `OR` conditions don’t have any technical restrictions, however may become unwieldy to maintain if they are too complex.
Aggregate functions are supported in place of single columns for all operators in the conditions array. Currently the aggregate functions `AVG()`, `COUNT()`, `MAX()`, `MIN()` and `SUM()` are supported. The following example will return all users who are part of two or more groups:
Conditions using aggregate functions will be automatically placed into the `HAVING` clause of the query that is executed.
In addition to being able to compare columns to values, it is also possible to compare two columns with each other. These comparisons use a slight modified version of the standard operators, where a `:` is appended.
- `=:` - compare equality of two columns - `!:` - compare inequality of two columns - `<:` - if the first column is less than the second - `<=:` - if the first column is less than or equal to the second - `>:` - if the first column is greater than the second - `>=:` - if the first column is greater than or equal to the second
It is also possible to use the column comparison operators with aggregate functions.
When building a record set from conditions, it is also possible to pass an array of `$order_bys` to specify the order in which the records are returned. The `$order_bys` parameter is an associative array of the column name, or expression, to order by as the key, and the direction to sort as the value.
The following example sort the users by their status, in an ascending manner:
In addition to being able to sort by columns, it is also possible to sort by expressions, such as `CASE` statements, and by aggregate functions, such as `COUNT()`, on related tables. The following example sorts by the number of groups the user is part of in a descending manner:
In certain situations it may be necessary to create a record set from record objects that have already been loaded from the database. The method ::buildFromArray() requires two parameters, the `$class` of record to create the set for and an array of `$records` to put in the set.
It is possible to pass information for use with pagination as the `$total_records`, `$limit` and `$page` parameters. These values power the methods ::getLimit(), ::getPage() and ::getPages(). The actual pagination of the records should either be done before the array is passed, or via the ::slice() method.
With `buildFromArray()` it is possible to create a record set from two or more different types of records. To create such a set, the `$class` parameter should be changed to an array containing each class name and the `$records` array should contain the different objects.
Please note that creating a record set from more than one kind of record will disable certain manipulation methods, such as retrieving primary keys and preloading related data. The [#Manipulation] contains details about what methods and features are unavailable in such a circumstance.
There are a number of limitations when using a conditions array to create a record set. Specific types of `JOIN` operations, `GROUP BY` clauses and `OR` conditions are impossible, or can not be tailored to the situation. In such instances, the ::buildFromSQL() method allows a raw Flourish SQL statement to be passed as the source for the records.
The first parameter is the `$class` of records to create and the second is the `$sql` to retrieve the records' data.
The SQL statement passed to `buildFromSQL()` should select every column for the table for the record type specified. It will often also be desired to ensure that statements using `JOIN`s along many-to-many relationships have an appropriate `GROUP BY` clause to ensure that duplicate records are not returned.
If a `LIMIT` clause is used in the SQL passed to `buildFromSQL()`, it is recommended to pass a third parameter, `$non_limited_count_sql`, containing a SQL query that will return the number of rows that would be returned if no `LIMIT` clause was present. This additional SQL statement powers the functionality of `->count(TRUE)`, which counts the number of records that would be returned with no `LIMIT` clause.
In addition to the `$non_limited_count_sql`, the `$limit` and `$page` parameters should be passed to power the ::getLimit(), ::getPage() and ::getPages() methods.
When using ::buildFromSQL(), you will usually need to include one or more dynamic values. Instead of passing a SQL string to `$sql` or `$non_limited_count_sql`, an array may be passed that contains a SQL string plus any values to escape into it.
Results from both [#Call] and [#Map] operations can be turned into record sets by the ::buildFromCall() and ::buildFromMap() methods. These methods take the exact same parameters as ::call() and ::map(), but take a resulting array of fActiveRecord objects and turn them into an fRecordSet.
When two classes are related to each other through another table, building a record set from related records is often the easiest way to get what you are looking for. While is is possible to use ::buildFromCall() with a `createRelatedRecord()` method from the fActiveRecord class, there is a built-in dynamic `build` method that does all of this for you.
The dynamic `build` methods also take advantage of the [#PreloadingRelatedRecords] functionality to improve database performance.
There are a number of different ways that records can be retrieved from a record set.
One of the most basic operations for a record set is iteration. Just like an array, an fRecordSet can be used with a `foreach` loop to access each record sequentially.
The method ::getRecord() will return the record at the index specified. This is good for retrieving a single record out of the set, but will throw an fNoRemainingException if there is no record to fetch.
It is also possible to use array-style referencing to accomplish the same result as ::getRecord().
To retrieve an array of all records in the set, simply call the method ::getRecords().
In addition to retrieving the record from a record set, other information about the set and records is available.
The size of a record set can often be important since it affect if code can be executed or not. The ::count() method gives a simple total containing the number of records in the set:
In the situation that a record set is actually a `LIMIT`ed number of records from the full query results, a single `TRUE` value can be passed to ::count() to retrieve the total number of records that exist.
It is also possible to throw an exception if no records are contained in a set. The method ::tossIfEmpty() will throw an fEmptySetException if called on a record set with zero records. By default the exception will contain a message indicating that no matching records could be found. It is possible to set a custom message by passing it as the first parameter to `tossIfEmpty()`.
If a limit was specified when calling ::build(), it will be available from ::getLimit().
If a limit was specified when calling ::build(), it will be available from ::getPage().
The number of pages will be available from ::getPages().
If only the primary keys of the records are needed, the method ::getPrimaryKeys() does just that.
The method ::getClass() will return the class (or classes) of the record in the record set.
Once a record set has been created, it can be manipulated in a number of different ways to retrieve information. A number of array-like functions are built into the class, and there is support for the map, reduce and filter operations common in functional programming.
The map, reduce and filter operations are declarative constructs that are common in functional programming. Map applies a function or method to a set of records and returns the results. Filter applies a function/method to each record and uses the return value to determine if a record should be removed. Reduce uses a function/method to convert all records into a single value via an iterative process of passing two values to the function at a time. Call is a feature implemented on fRecordSet that is not normally included with map, reduce and filter. Call allows a method to be called on every object in the record set, returning all of the values as an array.
PHP include a built-in `array_map()` function that allows an array of values to be passed to a callback. This works great for callbacks that require only one parameter, however if two or more parameters are required then all parameters must be arrays of equal length. In practical terms, this requires heavy usage of `array_fill()` and leads to code that is a pain to write and difficult to read.
The ::map() method in fRecordSet provides some features to reduce the extra work. By default when calling `map()`, each record will be passed to the callback as the first parameter.
If the callback takes more than one parameter, extra parameters can be passed to `map()`. Any parameter that is not an array will automatically be converted, so there is no need to calls to `array_fill()`. The example below would cause the `$upper` parameter to be set to `TRUE` for every record.
Another important feature is that the output of a method call to each record can be passed to the callback by passing a string such as `'{record}::methodName'`. If this is the case, the record will not be automatically passed as the first parameter. The example below will cause the output of the `getFirstName()` method to be passed to fUTF8::lower():
It is also possible to pass the complete record (as opposed to just the output of a method) in a position other than the first parameter by passing `'{record}'`.
The ::reduce() method accepts a callback that accepts two values and iteratively performs an operation on the result of the last operation plus the next record. It uses the same dynamics as the function `array_reduce()`, except that the initial value can be of any data type.
The first call to the callback will pass `NULL` as the first parameter and the first record as the second parameter. All subsequent calls will pass the result of the last call as the first parameter and the next record as the second parameter.
It is also possible to seed the operation with an initial value other than `NULL` by passing it as the second parameter to `reduce()`.
The ::filter() method allows for creating a new record set by removing records from the existing set by checking with a callback, testing the result of a method call on the record, or by comparing with a conditions array. It functions almost identically to `array_filter()`, except for supporting other methods of checking beyond a simple callback.
In the most basic form, a callback is passed and each record is passed one at a time to the callback. If the callback returns a value equal to `FALSE` the record will not be copied to the new record set.
It is also possible to filter a record set based on the return value of a method. This is accomplished by passing a string in the form `'{record}::methodName'`. The example below will remove all users with no middle initial:
As a final option, it is also possible to filter records based on whether or not they match all of the conditions in a conditions array. The conditions array should contain keys that are method names followed by a comparison operator and values to make the comparison with. Below is an example of checking to see if a user’s status is `'Active'`:
The following operators are supported for values that are not arrays:
- `=`: If the output of the method equals the value - `!`: If the output of the method does not equal the value - `>`: If the output of the method is greater than the value - `>=`: If the output of the method is greater than or equal to the value - `<`: If the output of the method is less than the value - `<=`: If the output of the method id less than or equal to the value - `~`: If the output of the method contains the value (case insensitive) - `!~`: If the output of the method does not contain the value (case insensitive)
If the value is an array, the following operators are supported:
- `=`: If the output of the method is contained in the array - `!`: If the output of the method is not contained in the array - `~`: If the output of the method contains one of the values in the array (case insensitive) - `!~`: If the output of the method contains none of the values in the array (case insensitive) - `&~`: If the output of the method contains all of the values in the array (case insensitive)
The following example will filter out any users who don’t have the first name `John`, who don’t have the last name `Smith` or `Henry` or who were created on or before January 1st, 2008.
It is also possible to do a search through the output of multiple method using the `~` operator. Method names should be separated by the `|` character.
If the value is a string, it will be parsed as search terms, which allows for quoted phrases, will removed punctuation from words and will ignore stop words (such as "the' and "a"). If the only words found are stop words, they will be included. If the value is an array, the strings in the array will be searched for.
The following example will find all users who have the words and phrases `john`, `smith` and `west peabody` in the output of any combination of `getFirstName()`, `getLastName()`, `getAddress()` and `getCity()`:
It is possible to perform an `OR` comparison using `|` separated list of method names with operators and the value to be an array of values, with one for each method. For example, the following code will check if `getFirstName()` returns `John` or `getEmailAddress()` contains `smith.com`:
The last supported operator in the intersection operator `><`. This operator checks to see if two columns that form a range intersect in any way with two values that form a range. This is most useful for dates, but can be used with numbers too.
The following example checks to see if `getStartDate()` and `getEndDate()` form a date range that includes any days between January 1, 2010 and January 3, 2010:
The optional boolean second parameter, `$remember_original_count`, will save the number of records in the current record set as the non-limited count on the new set. See the section on [#Size] for details about how to retrieve this number.
The ::call() method of fRecordSet returns an array of the return values from a call to a method on each record in the set. The first parameter is a string with the method to call.
It is also possible to pass parameters to the method by adding them to the `call()` method. The following example will pass `TRUE` to each call of the method `prepareLink()`:
While the fRecordSet class uses a minimal number of database queries to fetch a set of records, in turn getting records related to each of those will cause at least one database query to be executed per record. The three actions `prebuild`, `precount` and `precreate` all allow for running a single database query to fetch records related to every record in the set.
For example, if a record set of users needs to be displayed included how many comments they have left on a blog, the blog comments for each will need to be counted. Normally a call to `countBlogComments()` be all that is necessary. However, if 100 users are displayed on a page then an additional 100 database queries would be performed. Calling `precountBlogComments()` on the record set would cause a single database query to be executed to collect the counts for each user.
The `prebuild` method action would be appropriate to call when it is necessary to build a set of related records that are in a many-to-many or one-to-many.
The `precreate` method action allows creating objects from a column that is part of a `FOREIGN KEY` constraint.
In a manner similar to arrays, an fRecord contains quite a number of methods to add, remove and change records in a record set.
The ::slice() method takes up to two parameters, the zero-based `$offset` to start slicing at and the `$length` of a slice to make. If the `$offset` is negative, the slice will start that many records from the end of the set. If the `$length` is negative, the slice will stop that many records from the end of the set. If the `$length` is omitted or `NULL`, all records until the end of the set will be returned.
The following will create a new record set from the first three records.
The optional boolean third parameter, `$remember_original_count`, will save the number of records in the current record set as the non-limited count on the new set. See the section on [#Size] for details about how to retrieve this number.
If `$remember_original_count` is `TRUE` and the slicing is done in such a way that a valid `$limit` and `$page` can be determined, they will be appropriately set on the new record set.
The ::merge() method accepts a single parameter, the `$records`, and returns a new record set containing all records from both. All of the records from the second record set will be found after the records from the first. The `$records` parameter will accept an fRecordSet, an array of fActiveRecord objects or a single fActiveRecord.
Please note that it is possible to merge records sets of different types of records. If a record set contains records of more than one class, however, the `prebuild`, `precount` and `precreate` method actions will be unavailable, along with the methods `getPrimaryKeys()`, `flagAssociate()` and `isFlaggedForAssociation()`.
The ::diff() method accepts a single parameter, the `$records` to remove from the current record set. The `$records` parameter will accept an fRecordSet, an array of fActiveRecord objects or a single fActiveRecord.
The optional boolean second parameter, `$remember_original_count`, will save the number of records in the current record set as the non-limited count on the new set. See the section on [#Size] for details about how to retrieve this number.
The ::intersect() method accepts a single parameter, the `$records` to create an intersection with the current record set. All records not in both will be removed. The `$records` parameter will accept an fRecordSet, an array of fActiveRecord objects or a single fActiveRecord.
The optional boolean second parameter, `$remember_original_count`, will save the number of records in the current record set as the non-limited count on the new set. See the section on [#Size] for details about how to retrieve this number.
The ::unique() method takes the current record set and removes all duplicate records, returning a new record set.
The optional boolean parameter, `$remember_original_count`, will save the number of records in the current record set as the non-limited count on the new set. See the section on [#Size] for details about how to retrieve this number.
For display purposes, it can be useful to segment an fRecordSet into multiple smaller fRecordSet objects. The ::chunk() and ::split() methods to exactly this.
The ::chunk() method accepts a `$number` of records to place in each resulting fRecordSet. The returned value will be an array of fRecordSet objects that each contain `$number` records, although the last one may have fewer if there are not enough to fill it.
The ::split() method accepts a `$number` of fRecordSet objects to return. The returned value will be an array of fRecordSet objects that each contain `ceil(total records/$number)` records, although the last set may have fewer if there are not enough to fill it.
The ::contains() method accepts a single fActiveRecord record and checks if is is present in the record set.
When building a record set from conditions, the records can be sorted by the `$order_bys` parameter. See the section [#Ordering] for more details.
Two methods are available with fRecordSet to reorder the records in the set after it has been created. The method ::sort() accepts two parameters, the `$method` to call to get the value to compare, and the `$direction` to sort those values in. The `$direction` can be either `'asc'` or `'desc'`. The sorting is done using fUTF8::inatcmp() for comparison. A new sorted fRecordSet object is returned.
If a different sorting method is required, the method ::sortByCallback() will be of interest. This method requires a single parameter, a `$callback` that accepts two records and returns a negative value if the first record is less than the second, `0` if they are equal, or a positive value if the first record is greater than the second.