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