How can I fetch a model instance, plus its includes, plus the includes' includes?

Question

The aim: the user logs in, and sees a list of the next-airing episodes of the TV shows they're subscribed to. So there are three regular tables (Users, TvShows, Episodes) and one join table (Subscriptions, which joins User and TvShow).

The models:

• TvShow
  • has_many :users, through: :subscriptions
  • has_many :episodes
  • has_many :subscriptions
• Episode, belongs_to :tv_show
• User
  • has_many :tv_shows, through: :subscriptions
  • has_many :subscriptions
• Subscription
  • belongs_to :tv_show
  • belongs_to :user

To get a list of shows a user is subscribed to, I can say current_user.subscriptions(includes: :tv_show).all.

Here's where I get stuck, though: I want to select the current user's subscriptions, including the TV shows, and including the episodes of that show.

If possible, even more specifically, I'd like to include only one episode: the episode with the lowest possible airtime value (airtime is epoch time) > Time.now.to_i (ie, the episode that airs next).

I've tried searching for this in the documentation and on Google, but it's a hard question for me to describe in 30 words or less, so I haven't had much luck solving it. I would love some help here.

Answer 1

Try the below code

current_user.subscriptions.includes({tv_show: :episodes}).where("episodes.airtime > ?", Time.now.to_i)

Answer 2

Add a has_one relationship to the latest episode:

class TvShow
  has_many :episodes
  has_one :latest_episode, -> { order(:airtime) }, class_name: 'Episode', 
end

---

User.eager_load(tv_shows: :latest_episode).find(1)

---

Query 1

SELECT  DISTINCT "users"."id" FROM "users" 
LEFT OUTER JOIN "subscriptions" ON "subscriptions"."user_id" = "users"."id" 
LEFT OUTER JOIN "tv_shows" ON "tv_shows"."id" = "subscriptions"."tv_show_id" 
LEFT OUTER JOIN "episodes" ON "episodes"."tv_show_id" = "tv_shows"."id" 
WHERE "users"."id" = ? LIMIT 1  [["id", 1]]

Query 2

SELECT 
    "users"."id" AS t0_r0, 
    "users"."created_at" AS t0_r1, 
    "users"."updated_at" AS t0_r2, 
    "tv_shows"."id" AS t1_r0, 
    "tv_shows"."created_at" AS t1_r1, 
    "tv_shows"."updated_at" AS t1_r2, 
    "episodes"."id" AS t2_r0, 
    "episodes"."tv_show_id" AS t2_r1, 
    "episodes"."airtime" AS t2_r2, 
    "episodes"."created_at" AS t2_r3, 
    "episodes"."updated_at" AS t2_r4 
FROM "users" 
    LEFT OUTER JOIN "subscriptions" 
        ON "subscriptions"."user_id" = "users"."id" 
    LEFT OUTER JOIN "tv_shows" 
        ON "tv_shows"."id" = "subscriptions"."tv_show_id" 
    LEFT OUTER JOIN "episodes" 
        ON "episodes"."tv_show_id" = "tv_shows"."id" 
WHERE "users"."id" = ? AND "users"."id" IN (1)  [["id", 1]]