How to make a complex SQL query with nested models?

Question

I have three models: Channel > Program > Episode

Channel has_many programs
Program belongs_to channel
Program has_many episodes
Episode belongs_to program

How to make this query?

* "Number of total Episodes of associated Channel which has the highest number of Programs. *

To be more detailed,

1. Find the Channel which has the highest number of programs.
2. Find number of episodes joining with programs that belong to that Channel.

I'm really stucked.

How to do this most efficiently instead of writing lines of codes and many queries?

Answer 1

I dont know how to do with active record. But you can solve this problem with sql,

for example

select top 1 c.channel_id, count(p.)  as count from Program as p inner join Channels as c on c.channel_id=p.channel_id group by  c.channel_id order by 2 desc