1

I'm having a bit of brain fade today and can't figure out how I should express this SQL query correctly using ActiveRecord/Squeel/ARel:

SELECT `d1`.* FROM `domain_names` d1
WHERE `d1`.`created_at` = (
  SELECT MAX(`d2`.`created_at`)
  FROM `domain_names` d2
  WHERE `d2`.`owner_type` = `d1`.`owner_type`
  AND `d2`.`owner_id` = `d1`.`owner_id`
  AND `d2`.`key` = `d1`.`key`
)

Any ideas?

Background: The DomainName model has a polymorphic owner as well as a "key" field that allows owners to have many different types of domain name. The query above fetches the latest domain name for each unique [owner_type, owner_id, key] tuple.

Edit:

Here's the same query using JOIN:

SELECT `d1`.* FROM `domain_names` d1
JOIN (
  SELECT `owner_type`, `owner_id`, `key`, MAX(`created_at`) max_created_at
  FROM `domain_names`
  GROUP BY `owner_type`, `owner_id`, `key`
) d2
  ON `d2`.`owner_type` = `d1`.`owner_type`
    AND `d2`.`owner_id` = `d1`.`owner_id`
    AND `d2`.`key` = `d1`.`key`
WHERE `d1`.`created_at` = `d2`.`max_created_at`
tristanm
  • 3,337
  • 2
  • 27
  • 40
  • I've decided to go down a different path and have a separate `DomainNameVersion` model to make querying easier and more efficient. However, I'm still very much interested in an answer to this as it'll be useful down the track. – tristanm Sep 05 '13 at 01:39

0 Answers0