mysql - Complex Filtering of Relations in Yii1 -

currently i'm having problem i'm not able solve elegantly. given (simplified) db-design

each video has many stats. point normal 1-n relation, there constraints.

i want fetch n videos eager loaded current stats. there *n current stats @ 1 time video, n amount of metrics (distinct metric_id's) stat same video_id*

to achieve found possible solution complex query follows:

select `end`, `id`, `name`, `metric_id`, `value` `video`  join (    select `start`, `end`, `video_id`, `metric_id`, `value`    (        select `start`, `end`, `video_id`, `metric_id`, `value`        `stat`        `video_id` = 1        order `end` desc    ) stats    group `video_id`, `metric_id` ) stats on stats.video_id = video.id `video_id` = 1 

sql fiddle

this simplified (as i'm querying 1 video. in finished solution want comprehend or multiple videos).

• innermost query: stats vor given video ordered end date descending (because want recent stats video).
• inner query: group grabbed stats video_id , metric_id, because want each stat video fetched once
• outer query: join filtered stats actual video.

---

so there complex filtering necessary relation. still want leverage yii's active record , eager load of it.

is there way "hook" relations joinig process (like more modern frameworks do), or there simplify here have overlooked?

if have relation set gii automatically, can following;

$stats = stat::model()->findallbyattributes(array('video_id' => 1), array('order' => 'end desc')); 

to access records , parent table records

foreach $stat $stats {      $stat->start;      $stat->end; // parent table records can accessed      $stat->video->name          }