Filtering Users with Associated Records by Specific Date in Rails

I'm facing a challenge in a Ruby on Rails application where I need to filter users along with their associated records (Program and Learning), based on a specific date range, but only include those associated records that fall within the given date range.

Models I have the following models:

class User < ApplicationRecord
  has_many :programs
  has_many :learnings
end

class Program < ApplicationRecord
  belongs_to :user
end

class Learning < ApplicationRecord
  belongs_to :user
end

Data Example Consider this data scenario:

User1 has: Program with created_at: Yesterday Learning with created_at: Today User2 has: Program with created_at: Today Learning with created_at: Yesterday

For instance, if I filter for Date.yesterday, I want to get:

User1 with only the Program from yesterday. User2 with only the Learning from yesterday.

Current Approach I've tried various approaches using ActiveRecord queries with joins, where, and eager_load, but I'm either getting users with all their associated records (regardless of the date) or facing issues with structurally incompatible queries.

Can someone suggest a Rails way to achieve this filtering effectively, ensuring that the result is an ActiveRecord::Relation object?

Requirement Given a start and finish date (for example, Date.yesterday), I need to fetch users with their Program and Learning records that were created within this date range. However, the catch is that for a user, I only want to include the Program and Learning records that fall within the specified date range.

I've tried several methods, but none have given me the desired outcome:

1. Using joins and where: I attempted to use joins with where conditions to filter records. However, this approach either returns users with all their associated records (ignoring the date criteria) or leads to structurally incompatible queries due to the use of or.

Copy code
User.joins(:program, :learnings)
    .where(program: { created_at: start_date..finish_date })
    .or(User.joins(:program, :learnings)
        .where(learnings: { created_at: start_date..finish_date }))

OR

User.joins("LEFT JOIN programs ON programs.user_id = users.id AND programs.created_at BETWEEN '#{start_date}' AND '#{finish_date}'").joins("LEFT JOIN learnings ON learnings.user_id = users.id AND learnings.created_at BETWEEN '#{start_date}' AND '#{finish_date}'")

2. Subqueries with where: I also experimented with subqueries inside where, but it didn't filter the associated records based on the date criteria.

User.where(id: Program.select(:user_id).where(created_at: start_date..finish_date))
    .or(User.where(id: Learning.select(:user_id).where(created_at: start_date..finish_date)))