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…
- … 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)
- … were an actual customer and not a business.
- .where(“orderable_type = ‘Customer'”)
Things to Know
- Written for Rails 3.2 so it uses .uniq instead of .distinct (.uniq is deprecated).
- The initial .where operation becomes a single query when it’s executed.
- Combing the .where with .update_all means only two total queries were needed to grab the records I wanted and update them.
- The indented formatting starting with . (dot) makes it easy to build and read these subqueries. I love this formatting style.
Things to Learn
- 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.