Reason #571 Why Rails is Friggin' Awesome, Easily handle complex SQL statements. Options

[mipadi]

I'm working on a Rails app, and I was recently faced with an issue in which I had up retrieve records from a database based on a count of related records OR a field on the related record. Here's the SQL statement that ultimately gets written:

SQL

SELECT
"machines"."id" AS t0_r0,
"machines"."machine_name" AS t0_r1,
"machines"."hostname" AS t0_r2,
"machines"."mac_address" AS t0_r3,
"machines"."ip_address" AS t0_r4,
"machines"."hard_drive" AS t0_r5,
"machines"."ram" AS t0_r6,
"machines"."machine_type" AS t0_r7,
"machines"."use" AS t0_r8,
"machines"."comments" AS t0_r9,
"machines"."in_use" AS t0_r10,
"machines"."model" AS t0_r11,
"machines"."vendor_id" AS t0_r12,
"machines"."operating_system_id" AS t0_r13,
"machines"."location" AS t0_r14,
"machines"."acquisition_date" AS t0_r15,
"machines"."rpi_tag" AS t0_r16, "machine_updates"."id" AS t1_r0,
"machine_updates"."date" AS t1_r1,
"machine_updates"."details" AS t1_r2,
"machine_updates"."updated" AS t1_r3,
"machine_updates"."updated_by" AS t1_r4,
"machine_updates"."machine_id" AS t1_r5
FROM "machines" LEFT OUTER JOIN "machine_updates" ON machine_updates.machine_id = machines.id
WHERE (machine_updates.date < '2009-12-09 18:04:38.890205' OR machine_updates.machine_id IS NULL)
ORDER BY machine_updates.date DESC, machine_name ASC

Which would normally be a bit complicated and time-consuming to write out by hand, especially if you're trying to be database-agnostic (i.e., I'd like this to work with PostgreSQL, SQLite, and MySQL). Obviously it could be simplified a bit (SELECT * FROM machines, for example), but it's still annoying.

However, with it boils down to a nice line of Ruby code:

CODE

named_scope :needs_updates,
            :include => :machine_updates,
            :conditions => ['machine_updates.date < ? OR machine_updates.machine_id IS NULL', UPDATE_THRESHOLD.days.ago],
            :order => 'machine_updates.date DESC, machine_name ASC'

God I like Rails.