Complex Rails Active Record query to select record with true result for that day else latest updated else created record for day

Question

I am having following important columns in a table,

• customer_approval (boolean)
• created_at (datetime)
• updated_at (datetime)

There is a possibility that multiple records might be present for the day. One these records for a day might have customer_approval marked as TRUE.

How can we write a query to get result set as follows:

1. select only one record per day even if multiple are present for that day
2. First preference for selection of record must be given to record where customer_approval is true
3. If records don't fit in the above criteria select the latest entry which was created or updated based on created_at or updated_at column for that day
4. Select all the records based on a date range (From date - To Date) when they were created.

Also adding an example: Table Data

+--------+-------------------+------------+------------+
| row_id | customer_approval | created_at | updated_at |
+--------+-------------------+------------+------------+
|      1 | TRUE              | 2017-12-01 | 2017-12-01 |
|      2 | FALSE             | 2017-12-01 | 2017-12-01 |
|      3 | NIL               | 2017-12-01 | 2017-12-01 |
|      4 | NIL               | 2017-12-02 | 2017-12-02 |
|      5 | FALSE             | 2017-12-03 | 2017-12-03 |
|      6 | NIL               | 2017-12-03 | 2017-12-03 |
|      7 | NIL               | 2017-12-04 | 2017-12-05 |
+--------+-------------------+------------+------------+

Expected ResultSet for Date Range 2017-12-01 & 2017-12-05 :

+--------+-------------------+------------+------------+
| row_id | customer_approval | created_at | updated_at |
+--------+-------------------+------------+------------+
|      1 | TRUE              | 2017-12-01 | 2017-12-01 |
|      4 | NIL               | 2017-12-02 | 2017-12-02 |
|      6 | NIL               | 2017-12-03 | 2017-12-03 |
|      7 | NIL               | 2017-12-04 | 2017-12-05 |
+--------+-------------------+------------+------------+

Thanks in advance.

P.S. Sorry about the poor table formatting.

Answer 1

Postgresql query

SELECT DISTINCT ON (created_at) row_id, customer_approval, created_at
FROM the_table
ORDER BY customer_approval DESC, created_at DESC;

In rails

TheTable.select("DISTINCT ON (created_at) row_id, customer_approval, created_at")
        .order(customer_approval: :desc, created_at: :desc )

Answer 2

I know it's not perfect, and you should think about exceptions.

Model

class Customer < ApplicationRecord

  scope :approval, -> { where customer_approval: true }
  scope :waiting, -> { where customer_approval: nil }

  scope :for_day, ->(date) {
    where(
      arel_table[:created_at].eq(date)
      .or(arel_table[:updated_at].eq(date))
    )
  }

  def self.last_change
    order(created_at: :desc, updated_at: :desc).first
  end
end

Controller

def fetch_customers(first, last)
  result = []

  first.upto(last) do |date|
    customer = Customer.for_day(date).approval.first
    customer ? (result << customer) : (result << Customer.for_day(date).waiting.last_change)
  end

  result
end

# first = Date.parse('2017-12-01')
# last = Date.parse('2017-12-05')
# fetch_customer(first, last)

P.s. Hope your rails version 4+ (for Arel table)