I want to explain that I prefer other way of manipulating of data then more common :
$posts=Post::model()->with('author')->findAll();
Say for selection of Categories I use function:
public function getCategoriesList( $list_output_format, $page = '', $filters = array(), $sort = '', $sort_direction = '' )
{
if ( $list_output_format!= Appfuncs::$LIST_OUTPUT_FORMAT_COUNT and $list_output_format!= Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER and $list_output_format!= Appfuncs::$LIST_OUTPUT_FORMAT_LIST ) {
$list_output_format!= Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER; // default value
}
$enablePagination= Yii::app()->controller->app_config['enablePagination'];
$admin_pageSize= Yii::app()->controller->app_config['admin_pageSize'];
$pagination_filters= Appfuncs::copy_filters_array( $filters, 'filter_',true);
if ( empty($sort) ) $sort= 'C.name';
if ( $list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_COUNT ) $page= '';
$criteria=new CDbCriteria;
if ( !empty($filters['show_related_tours_count']) and $filters['show_related_tours_count']) {
$criteria->select = ' ( select count(*) from tbl_tour as T where T.category_id = C.id ) as related_tours_count, C.* ';
}
$criteria->alias = 'C';
if (!empty($filters['name'])) {
$criteria->compare( 'name', $filters['name'], true );
}
if ( !$enablePagination ) {
$PaginationObj= false;
$criteria->limit= -1;
$criteria->offset= -1;
} else {
$PaginationObj= ( $list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER ? array('pageSize'=> $admin_pageSize,
'params'=> $pagination_filters ) : false );
}
if($list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_COUNT) {
return Category::model()->count($criteria);
} else {
$criteria->order = $sort . ' ' . $sort_direction;
$ActiveDataProvider= new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria,
'pagination'=> $PaginationObj,
));
if ( $list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER ) {
return $ActiveDataProvider;
}
if ( $list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_LIST ) {
return $ActiveDataProvider->getData();
}
}
}
The main advantage is that setting to $list_output_format different value I get 3 different results type:
Appfuncs::$LIST_OUTPUT_FORMAT_COUNT - I need to get number of objects
Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER - get List of data in object for filling of CListView
Appfuncs::$LIST_OUTPUT_FORMAT_LIST ) - get List of data for manual filling of say selection input
In $filters array are names/values of filters. If flag show_related_tours_count is set then to this sql column related_tours_count based on subquery is added.
If for this table something is changed, say new fields added or for some fields condition is changed then I have to change only 1 function, not sevearl.
Actually that was the reason(when I used Symfony1(propel)), I started writing such common function instead of several functions.
In control I wrote like :
$filters_array = array('name' => $filter_name, 'show_related_tours_count'=>true);
$CategoriesList = Category::model()->getCategoriesList( Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER, $Category_page, $filters_array, $sort, $sort_direction);
For the table with many fields there can be several conditions. Say for Tour table with many fields and many search criterias:
<?php
public function getToursList($list_output_format, $Tour_page = '', $filters = array(), $sort = '', $sort_direction = '')
{
if ($list_output_format != Appfuncs::$LIST_OUTPUT_FORMAT_COUNT and $list_output_format != Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER and $list_output_format != Appfuncs::$LIST_OUTPUT_FORMAT_LIST) {
$list_output_format != Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER;
}
$enablePagination = Yii::app()->controller->app_config['enablePagination'];
$admin_pageSize = Yii::app()->controller->app_config['admin_pageSize'];
$pagination_filters = Appfuncs::copy_filters_array($filters, 'filter_', true);
if (empty($sort)) $sort = 'T.name';
if ($list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_COUNT) $Tour_page = '';
$criteria = new CDbCriteria;
$criteria->alias = 'T';
if (!empty(self::$data_options['show_belongs_to']) and self::$data_options['show_belongs_to']) {
$criteria->with[] = 'belongs_to_state'; // table Tour may have 4 related tables
$criteria->with[] = 'belongs_to_category';
$criteria->with[] = 'belongs_to_subregion';
$criteria->with[] = 'belongs_to_region';
}
$sql_select_string = '';
$is_tour_stars = false;
if (!empty($filters['show_reviews_count']) and $filters['show_reviews_count']) { // show reviews_count field based on subquery
$tour_stars_text = '';
if (!$is_tour_stars) {
$is_tour_stars = true;
$tour_stars_text = ', T.*';
}
$sql_select_string .= ($sql_select_string != '' ? ',' : '') . ' ( select count(*) from tbl_tour_review as TR where TR.tour_id = T.id and TR.status = \'A\' ) as reviews_count ' . $tour_stars_text;
}
if (!empty($filters['show_reviews_avg_rating']) and $filters['show_reviews_avg_rating']) { // show reviews_avg_rating field based on subquery
$tour_stars_text = '';
if (!$is_tour_stars) {
$is_tour_stars = true;
$tour_stars_text = ', T.*';
}
$sql_select_string .= ($sql_select_string != '' ? ',' : '') . ' ( select avg(TR.stars_rating_type_id) as stars_rating_type_id from tbl_tour_review as TR where TR.tour_id = T.id and TR.status = \'A\' ) as reviews_avg_rating ' . $tour_stars_text;
}
if (!empty($sql_select_string)) {
$criteria->select = $sql_select_string;
}
if (!empty($filters['feature'])) { // several filters - any can be filled or empty
$criteria->compare('T.feature', $filters['feature']);
}
if (!empty($filters['status'])) {
$criteria->compare('T.status', $filters['status']);
}
if (!empty($filters['region_id'])) {
$criteria->compare('T.region_id', $filters['region_id']);
}
if (!empty($filters['subregion_id'])) {
$criteria->compare('T.subregion_id', $filters['subregion_id']);
}
if (!empty($filters['category_id'])) {
$criteria->compare('T.category_id', $filters['category_id']);
}
if (!empty($filters['state_id'])) {
$criteria->compare('T.state_id', $filters['state_id']);
}
if (!empty($filters['user_id'])) {
$criteria->compare('T.user_id', $filters['user_id']);
}
if (!empty($filters['price_from']) and !empty($filters['price_till'])) {
$criteria->addBetweenCondition('T.price', $filters['price_from'], $filters['price_till'] /*, 'OR'*/);
} else {
if (!empty($filters['price_from'])) {
$criteria->compare('T.price', '>=' . $filters['price_from']);
}
if (!empty($filters['price_till'])) {
$criteria->compare('T.price', '<' . $filters['price_till']);
}
}
if (empty($filters['sort_by'])) { // sort by given columns
$criteria->order = $sort . ' ' . $sort_direction;
} else {
switch ($filters['sort_by']) { // sort by given conbination of columns
case "tours_by_feature_price":
$criteria->order = ' T.feature asc, T.price asc ';
break;
case "review_rating":
$criteria->order = 'T.feature asc, reviews_avg_rating desc';
break;
case "tours_by_feature_name":
$criteria->order = 'T.feature asc, T.name asc';
break;
}
}
$criteria->join = 'LEFT JOIN tbl_category as c ON c.id = T.category_id';
if (!$enablePagination) {
$PaginationObj = false;
$criteria->limit = -1;
$criteria->offset = -1;
} else {
$PaginationObj = ($list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER ? array('pageSize' => $admin_pageSize,
'params' => $pagination_filters) : false);
}
if ($list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_COUNT) {
return Tour::model()->count($criteria);
} else {
$ActiveDataProvider = new CActiveDataProvider(get_class($this), array(
'criteria' => $criteria,
'pagination' => $PaginationObj,
));
if ($list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER) {
return $ActiveDataProvider;
}
if ($list_output_format == Appfuncs::$LIST_OUTPUT_FORMAT_LIST) {
$ListData = $ActiveDataProvider->getData();
foreach ($ListData as $key => $TourRow) {
if (!empty($filters['show_default_image'])) { // in case we need to show image of tour - fill this field
$ListData[$key]->default_image_path = $this->get_default_image_path($TourRow->id);
}
}
return $ListData;
}
}
}
And in controll I call it :
$ToursObj = Tour::model( Tour, array('show_belongs_to'=>true) )->getToursList(Appfuncs::$LIST_OUTPUT_FORMAT_DATA_PROVIDER, $page,
array( 'category_id'=> $category_id, 'state'=> $state, 'status'=>'A', 'state'=> $state,'active_tours_list'=>$filter_active_tours_list, 'flag_show_default_image'=>true, 'tours_rows_on_page'=>$tours_rows_on_page, 'show_reviews_count'=>true, 'show_reviews_avg_rating'=>true, 'show_default_image'=> true, 'sort_by'=>$sort_by), $sort,
$sort_direction );
?>
I used similar data retrieve functions in Symfony 1 with propel and in CI with ActiveRecord. You can say that above function is to big and
programmer can confuse something: programmer can alway confuse himself and others and all make errors.
As for me this function is well structered. For this table in backend and frontend can be filled different combinations of search fields or non at all.
And it seems to me more flexible(maybe I ma wrong) then to write similar in syntax like below:
$posts=Post::model()->with('author')->findAll();
And again, if for this table something is changed, say new fields added or for some fields condition is changed then I have to change only 1
function, not several functions.
And here I come to the point I started the topic that fer this big Tour table I show data of several related table
and I want to add condition if to show data of several related tables or not like rows in getToursList method :
if (!empty(self::$data_options['show_belongs_to']) and self::$data_options['show_belongs_to']) {
But before calling getToursList I have someway to set ‘show_belongs_to’ parameter also for public function relations() of this model :
as relations is called before getToursList() in calling
Tour::model( )->getToursList(...
I hope I explained clearly what I want...
Thank you for your patience!