Advanced ActiveRecord querying

Ruby on Rails
1

Having gone through the Advanced ActiveRecord Querying course I’m still at a loss how to implement the following spec without resorting to mapping/selecting and the like using Ruby.

I have ‘Companies’ that have many ‘Employees’.

One of the company’s employees is flagged as the ‘contact point’ (boolean field). Mapped to a scope contact_points.

Companies also have many ‘Orders’.

The challenge is to query all contact point employees across a time period and return those who’s company placed their first order during the period. The output is used for an internal company report.

So it’s a combination of finding the minimum created_at time for orders, grouped by company_id then working back to the company to find the contact point employee. (I think).

After lots of huffing and puffing trying to use pure ActiveRecord/SQL solutions I’ve resorted to this.

Employee
  .contact_points
  .joins(:company)
  .where(
    companies: {
      id: Order.all.group_by(&:company_id)
            .map{ |o| o.last.sort{ |a,b| a.created_at <=> b.created_at }.first }
            .keep_if{ |o| (date_from..date_to).cover?(o.created_at) }
            .map(&:company_id)
    }
  )

date_from and date_to are scrubbed date params from the report form.

I’m hoping there may be a way to simplify this for future me?

Thanks in advance.