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,

Find the Channel which has the highest number of programs.
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?

@Mihai active record query is independent of databases right? — Raj
– Raj, Commented Jan 21, 2014 at 10:30
@emaillenin To be frank,I had no idea what active record is. — Mihai
– Mihai, Commented Jan 21, 2014 at 10:31
It's postgresql @Mihai. Any help with even with SQL appreciated. — scaryguy
– scaryguy, Commented Jan 21, 2014 at 10:55
So you want to find the total count of episodes for the channel with the greatest number of programs? What treatment do you want where there are many channels that all have the greatest number of programs, but presumably different numbers of episodes? — David Aldridge
– David Aldridge, Commented Jan 21, 2014 at 12:01

janet · Accepted Answer · 2014-01-23 13:53:51Z

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

answered Jan 23, 2014 at 13:53

janet

437 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make a complex SQL query with nested models?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related