title: Ruby / Datamapper query comparing columns from different tables
body: <p>I currently have a fairly simple database that is regularly updated by a script. I would like to filter the results by comparing the equality of 2 columns. The SQL query would look like:</p>
  
  <pre>
  SELECT devices.site_name, devices.device_name, devices.created_at
  FROM devices, locations 
  WHERE devices.created_at = locations.most_recent_updated_time AND devices.site_name = locations.site_name;
  </pre>
  
  <p>Here's my datamapper model</p>
  
  <pre><code>class Device
    include DataMapper::Resource
  
    property :id, Serial
    property :device_name, Text, :required => true
    property :site_name, Text, :required => true
    property :created_at, DateTime
  
    belongs_to :location 
  
  end
  
  class Location
    include DataMapper::Resource
  
    property :site_name, Text, :required => true, :key => true
    property :most_recent_updated_time, DateTime, :required => true
  
    has n, :devices
  end
  </code></pre>
  
  <p>Each time the script runs it appends new Device entries and updates the `:most_recent_updated_time field in the Location table. Here's an example for how the tables are updated.</p>
  
  <pre>Devices
  ╔════╦═════════════╦═══════════╦═══════════════════════════╗
  ║ ID ║ Device_Name ║ Site_Name ║        Created_at         ║
  ╠════╬═════════════╬═══════════╬═══════════════════════════╣
  ║  1 ║ Device1     ║ Dallas    ║ 2013-07-23T16:59:12-04:00 ║
  ║  2 ║ Device2     ║ Dallas    ║ 2013-07-23T16:59:12-04:00 ║
  ║  3 ║ Device3     ║ Chicago   ║ 2013-07-23T16:59:12-04:00 ║
  ╚════╩═════════════╩═══════════╩═══════════════════════════╝
  Location
  ╔═══════════╦═══════════════════════════╗
  ║ Site_Name ║ Most_Recent_Updated_Time  ║
  ╠═══════════╬═══════════════════════════╣
  ║ Dallas    ║ 2013-07-23T16:59:12-04:00 ║
  ║ Chicago   ║ 2013-07-23T16:59:12-04:00 ║
  ║ Boston    ║ 2013-07-23T16:59:12-04:00 ║
  ╚═══════════╩═══════════════════════════╝
  </pre>
  
  <p>After Script Run</p>
  
  <pre>
  Devices
  ╔════╦═════════════╦═══════════╦═══════════════════════════╗
  ║ ID ║ Device_Name ║ Site_Name ║        Created_at         ║
  ╠════╬═════════════╬═══════════╬═══════════════════════════╣
  ║  1 ║ Device1     ║ Dallas    ║ 2013-07-23T16:59:12-04:00 ║
  ║  2 ║ Device2     ║ Dallas    ║ 2013-07-23T16:59:12-04:00 ║
  ║  3 ║ Device3     ║ Chicago   ║ 2013-07-23T16:59:12-04:00 ║
  ║  4 ║ Device4     ║ Dallas    ║ 2013-07-24T18:00:00-04:00 ║
  ║  5 ║ Device5     ║ Chicago   ║ 2013-07-24T18:00:00-04:00 ║
  ╚════╩═════════════╩═══════════╩═══════════════════════════╝
  Location
  ╔═══════════╦═══════════════════════════╗
  ║ Site_Name ║ Most_Recent_Updated_Time  ║
  ╠═══════════╬═══════════════════════════╣
  ║ Dallas    ║ 2013-07-24T18:00:00-04:00 ║
  ║ Chicago   ║ 2013-07-24T18:00:00-04:00 ║
  ║ Boston    ║ 2013-07-24T18:00:00-04:00 ║
  ╚═══════════╩═══════════════════════════╝
  </pre>
  
  <p>So if I run the query after the script runs, I'd like it to return Device4 and Device5.</p>