Seems like I finally have this functioning from all my testing.
Given these models:
Project Model
PHP Code:
<?php
class Project extends ActiveRecord {
public static $fields = array('id','user_id','category_id','range_id','title','message','status','created');
public static $transformations = array(
'created'=>array(
'save'=>'FROM_UNIXTIME({this})'
)
);
public static $foreignKeys = array(
'user_id'=>array('User','id')
,'category_id'=>array('Category','id')
,'range_id'=>array('Range','id')
);
public static $dataTypes = array(
'id'=>'int(10) unsigned'
,'user_id'=>'int(10) unsigned'
,'category_id'=>'int(10) unsigned'
,'range_id'=>'int(10) unsigned'
,'title'=>'varchar(40)'
,'message'=>'text'
,'status'=>'tinyint unsigned'
,'created'=>'timestamp'
);
public static $links = array(
'User'=>array(
'status'=>'status'
)
);
public static $primaryKey = 'id';
public static $requiredFields = array('user_id','category_id','range_id','title','message','status','created');
public static $hasMany = array('bids');
public static $hasOne = array('won_project');
public static $belongsTo = array('user','range','category');
public static function find() {
$args = func_get_args();
return parent::_find(__CLASS__,$args);
}
}
?>
WonProject Model
PHP Code:
<?php
class WonProject extends ActiveRecord {
public static $fields = array('id','project_id','bid_id','created');
public static $requiredFields = array('project_id','bid_id','created');
public static $transformations = array(
'created'=>array(
'save'=>'FROM_UNIXTIME({this})'
)
);
public static $primaryKey = 'id';
public static $uniqueKeys = array('project_id','bid_id');
public static $belongsTo = array('project','bid');
public static $foreignKeys = array(
'bid_id'=>array('Bid','id')
,'project_id'=>array('Project','id')
);
public static function find() {
$args = func_get_args();
return parent::_find(__CLASS__,$args);
}
}
?>
Range Model
PHP Code:
<?php
class Range extends ActiveRecord {
public static $fields = array('id','min_value','max_value');
public static $primaryKey = 'id';
public static $hasMany = array('projects');
public static function find() {
$args = func_get_args();
return parent::_find(__CLASS__,$args);
}
}
?>
The below would then create a navigation menu that has each range and the number of projects per a individual category or not category filter at all. Something that wouldn't be possible without a subquery.
PHP Code:
protected function _findRanges($pCategory=null) {
$wonProjects = WonProject::find(
ActiveRecord::findSelect
,array(
'empty'=>true
,'select'=>'project_id'
)
);
$find = array(
'select'=>'range_id'
,'group'=>'range_id'
,'status <>'=>0
,'id NOT IN'=>$wonProjects
,'dynamic'=>array(
'project_count'=>'COALESCE(COUNT(Project.id),0)'
)
);
if(!is_null($pCategory)) {
$find['category_id'] = $pCategory;
}
$projects = Project::find(
ActiveRecord::findSelect
,$find
);
return Range::find(
array(
'include'=>array(
$projects
)
,'sort'=>array('min_value'=>'ASC')
)
,array(
'require'=>false
,'association'=>array(
'id'=>'t1_range_id'
)
,'propertyType'=>'one'
,'rename'=>'subquery'
)
);
}
SQL:
Code SQL:
SELECT
t3.`t1_range_id` AS t1_range_id
,t3.`t1_id` AS t1_id
,t3.`t1_project_count` AS t1_project_count
,t2.`id` AS t2_id
,t2.`min_value` AS t2_min_value
,t2.`max_value` AS t2_max_value
FROM
ranges AS t2
LEFT
JOIN (
SELECT
t1.`range_id` AS t1_range_id
,t1.`id` AS t1_id
,COALESCE(COUNT(t1.id),0) AS t1_project_count
FROM
projects AS t1
WHERE
t1.STATUS <> ?
AND
t1.id NOT IN (
SELECT
t0.`project_id` AS t0_project_id
FROM
won_projects AS t0
)
GROUP
BY t1.range_id) AS t3
ON
t2.`id` = t3.`t1_range_id`
ORDER
BY
t2.min_value ASC
It kinda goes against the idea of a finder by returning the select object(query) but I think the advantages of this far outweigh the disadvantages.
Ultimately what would be returned is a collection object(array) with the below property hierarchy.
- Range Object
- id => 1
- min_value => 10
- max_value => 50
- subquery => null
- Range Object
- id => 5
- min_value => 500
- max_value => 1000
- subquery => Project Object
- range_id => 5
- id => 27
- project_count => 1
So to get the number of projects in range 5:
PHP Code:
$totalProjects = $ranges[1]->subquery?$ranges[1]->subquery->project_count:0;
Then to avoid conflicts and the save() method from parsing the objects in the hierarchy any related item can be renamed. In this case the related subquery was renamed to subquery. Otherwise the property name would default to the first model in the select of that statement potentially causing conflicts. So if the rename option were omitted the property representing the subquery info would be projects.
Bookmarks