CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
Adam Wiggall
Adam Wiggall

Reputation: 393

ActiveRecord querying in Rails

I'm at a loss how to implement the following spec without resorting to mapping/selecting and the like using Ruby and try to use SQL as much as possible.

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.

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?

Upvotes: 2

Views: 172

Answers (2)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24581

Your current approach slurps all orders into memory, which is going to be slow and crashy. I would do it like this:

Employee
  .contact_points
  .joins(:company)
  .where(<<-EOQ, date_from, date_to, date_from)
    EXISTS (SELECT  1 
            FROM    orders o
            WHERE   o.company_id = companies.id
            AND     o.created_at BETWEEN ? AND ?)
    AND NOT EXISTS (SELECT  1
                    FROM    orders o
                    WHERE   o.company_id = companies.id
                    AND     o.created_at < ?)
  EOQ

In fact unless there are other conditions you don't even need to join to companies, because you could just use employees.company_id.

EDIT: Or this might be more performant:

Employee
  .contact_points
  .joins(<<-EOQ)
    INNER JOIN (SELECT o.company_id, MIN(o.created_at) created_at
                FROM   orders o
                GROUP BY company_id) min_orders
    ON employees.company_id = min_orders.company_id
  EOQ
  .where("min_orders.created_at BETWEEN ? AND ?", date_from, date_to)

Upvotes: 1

Louis Derrick
Louis Derrick

Reputation: 1

Please refer to the links

http://guides.rubyonrails.org/active_record_querying.html

SELECT * FROM clients WHERE (clients.orders_count IN (1,3,5))

Upvotes: 0

Related Questions