Complex Filtering of Relations in Yii1

Question

Currently I'm having a problem which I'm not able to solve elegantly. Given is this (simplified) DB-Design

Each video has many stats. To this point it is a normal 1-n relation, however there are some constraints.

I want to fetch n videos eager loaded with their current stats. There are only *n current stats at one time for a video, where n is the amount of metrics (distinct metric_id's) for the stat with the same video_id*

To achieve that I found the only possible solution to be a really complex query which is as follows:

SELECT `end`, `id`, `name`, `metric_id`, `value` FROM `video` 
JOIN (
   SELECT `start`, `end`, `video_id`, `metric_id`, `value`
   FROM (
       SELECT `start`, `end`, `video_id`, `metric_id`, `value`
       FROM `stat`
       WHERE `video_id` = 1
       ORDER BY `end` DESC
   ) stats
   GROUP BY `video_id`, `metric_id`
) stats ON stats.video_id = video.id
WHERE `video_id` = 1

SQL Fiddle

This is simplified (as I'm only querying one video. In my finished solution I want to comprehend it to all or multiple videos).

• Innermost query: Get all stats vor the given video ordered by the end date descending (because I want only the most recent stats for the video).
• Inner query: Group the previously grabbed stats by the video_id and the metric_id, because I want each stat for the video only to be fetched once
• Outer Query: Join the filtered stats with the actual video.

---

So there is some really complex filtering necessary for that relation. Still I want to leverage Yii's Active Record and Eager Load all of it.

Is there any way to "hook" into the relations joinig process (like more modern frameworks do), or is there anything I could simplify here that I have overlooked?

Answer 1

If you have your relation set properly which gii does it automatically, Then you can do the following;

$stats = Stat::model()->findAllByAttributes(array('video_id' => 1), array('order' => 'end DESC'));

To access records and parent table records

foreach $stat as $stats {
     $stat->start;
     $stat->end;
// Parent table records can be accessed like so
     $stat->video->name         
}