Filter Relations using Eager Loading and Active Record

Hi together,

currently I’m having a problem which I’m not able to solve elegantly. That’s why I’m asking you for help.

Given is this (simplified) DB-Design.

i.imgur.com/RXhjeBw.png (Sorry I cannot post links)

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 it’s current stats.

There are only n current stats at one time for a video, where n is the number of metrics 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



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.

Here is a fiddle: sqlfiddle.com/#!9/9b553/1 (cannot post links)

See how there actually two stats with the metric_id of 1, but I am only fetching the most recent one?

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.

So is there any way to "hook" into the relations joing process, or is there anything I could simplify here that I have overlooked?

Best regards

Thomas

I guess DB view might be a good measure.

You can construct an AR wrapping it for retrieving the data.