Subqueries in ActiveRecord with Multiple Conditions & Distinct Select

I’ve recently started learning about database subqueries and how to do them with ActiveRecord. I’ve also learned a cool new formatting trick (at least using RubyMine IDE).

This is the beginning of my learning so don’t take this as great code. In fact, this is really here so I don’t forget it and it’s easily available the next time I need it (and I can work on making it better then).

In other words, this code is for me, not you. 🙂

start_day = DateTime.new(2017)
customers = Customer.where(id: WorkOrder
                                   .where(id: Payment
                                                  .where('location_id = 2 and payment_received_date >= ?', start_day)
                                                  .select(:work_order_id)
                                                  .uniq)
                                   .where("orderable_type = 'Customer'")
                                   .select(:orderable_id)
                                   .uniq)
customers.update_all(special_contact_required: true)

What it Does

I needed to email a subset of our customers that…

  1. … paid for a repair on or after January 1, 2017 at one of our specific stores.
    • .where(‘location_id = 2 and payment_received_date >= ?’, start_day)
  2. … were an actual customer and not a business.
    • .where(“orderable_type = ‘Customer'”)

Things to Know

  1. Written for Rails 3.2 so it uses .uniq instead of .distinct (.uniq is deprecated).
  2. The initial .where operation becomes a single query when it’s executed.
  3. Combing the .where with .update_all means only two total queries were needed to grab the records I wanted and update them.
  4. The indented formatting starting with . (dot) makes it easy to build and read these subqueries. I love this formatting style.

Things to Learn

  1. I couldn’t figure out a way to do the .select together with a .order. It wasn’t a big deal in this case (it was part of a migration), but in the future it will be necessary to sort them.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s