Data Model Fetch
From FuseWiki
Contents |
[edit] Introduction
The FuseDataModel class is designed to be highly customizable to suit any schema and to retrieve data in any manner possible through the underlying DBMS.
Most operations that retrieve data from the database at some point go through the fetch_all() method of the FuseDataModel class, although the method name is a bit misleading because you can fetch even just a single row through this method. Other methods, such as fetch_single() and fetch_all_by() actually just call fetch_all() with specific options passed via the first and only parameter to fetch_all(), an associative array of options.
[edit] Fetch Methods
Below is a list of the methods that can be used to fetch data from the database. Customization of queries and return types is done through various options, described below.
- fetch_all( [$options] ) - By default, fetches all rows in the model (select table.*) and returns a FuseModelIterator which can be used to iterate through the result.
- fetch_all_by(field_key_or_name, field_value [, $options] ) - Fetches all rows in the table where field_key_or_name=field_value. By default, returns a FuseModelIterator which can be used to iterate through the result.
- fetch_single( [$options] ) - For queries that can only return a single row, use this function to return a single FuseDataModel object instead of an iterator
- fetch_single_by(field_key_or_name, field_value [, $options] ) - For queries that can only return a single row, use this function to return a single FuseDataModel object instead of an iterator. Fetches all rows in the table where field_key_or_name=field_value.
[edit] Options for fetch methods
- query_obj - For queries that need advanced customization, you can pass a FuseSQLQuery object by setting this option key. Any clauses that have been added to your query object will be respected when the data is fetched from the database.
- include - Rather than manually specifying JOINs for models that already have their relationships set up properly (via has_many, belongs_to, etc), you can tell the data model to apply them here. This can be a single table name or an array of table names. You can even specify "recursive includes" (tables that are also being joined via the include option) by delimiting the tables with a period. For example:
$options['include'] = array( 'blog_entries', 'blog_entries.blog_comments' )
Additionally, you can set "as" (for table aliases) and "join type" options for your included tables like so:
$options['include'] = array( 'blog_comments' => array('as' => 'bc', 'join_type' => 'INNER') );
- order_by - Specify an order by clause as string such as "widget_id ASC" here. If you need more versatility in generating your query, use the query_obj option.
- return - By default, fetch functions return a FuseModelIterator. If you want the raw DB result, specify "resultset" here.
- select - Specify a SELECT clause as string such as "widgets.widget_name" here. If you need more versatility in generating your query, use the query_obj option.
- where - Specify a WHERE by clause as string such as "widget_id=3" here. If you need more versatility in generating your query, use the query_obj option.
- limit - Specify a LIMIT clause as string such as "10, 20" here. If you need more versatility in generating your query, use the query_obj option.
- group_by - Specify a GROUP_BY by clause as string such as "GROUP BY widget_id" here. If you need more versatility in generating your query, use the query_obj option.
- join - Specify a JOIN clause as string such as "LEFT JOIN widget_categories USING(widget_category_id)" here. If you need more versatility in generating your query, use the query_obj option. The include option should alleviate the need for most manual JOINs.
- query_obj_final - if TRUE, and a query object is passed to the fetch method, no base selections, joins, etc, will be added - the query will be generated exactly as it was set up.
- selections_final - if TRUE, no base selections will be added to the query, meaning you have to do your SELECTing manually.
[edit] Notes
- Because the show_list() method of the FuseDataController eventually passes its options to fetch_all()', you can specify any of the options above when calling parent::show_list() in your own controller classes. More information can be found in FuseDataController
[edit] Examples
[edit] Automatic filtering
FUSE::Require_model('BlogEntry'); //has a belongs_to relationship to user
$entry = new BlogEntry;
$entry->id = 5;
$entry_posted_by = $entry->users->name; //gets the username from the users table for this entry
[edit] Basic customization
public function myControllerMethod() {
FUSE::Require_model('BlogEntry');
$entry = new BlogEntry;
$options['limit'] = 10;
$options['order_by'] = 'entry_id DESC';
$this->blog_entries = $entry->fetch_all( $options );
$this->render(); //Your view can now iterate through <{ITERATOR blog_entries}>
}
[edit] Including multiple tables in your data list
Let's say you're in your ItemOrder controller, where the show_list() method is used to list out orders for your product. More than likely, you'll want to list the consumer name and the order status along with the list of orders, but these are in different tables! No problem, just add this to your Data controller:
FUSE::Require_class('AppControl/FuseDataController');
class MyController extends FuseDataController {
public $list_options = array( 'include' => array('customers', 'item_order_status_types') );
}
//
// More include options
//
class MyController extends FuseDataController {
public $list_options = array( 'include' =>
array( 'customers' => array('join_type' => 'INNER', 'alias' => 'c'),
'item_order_status_types')
);
}
[edit] Iterating through the data
By default, fetch() methods return a FuseModelIterator object. You can call methods such as next(), previous(), and seek() on the iterator to get to specific data rows. Below is an example of iterating through the returned data:
FUSE::Require_model('OrderStatusType');
$order_status_type = new OrderStatusType();
$all_types = $order_status_type->fetch_all();
while ( $cur_type = $all_types->next() ) {
echo "Current type name: " . $cur_type->name . '<br />';
echo "Current type id: " . $cur_type->id . '<br />';
}
