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.