Named Scope to only search first results of joined table

Question

I'm having trouble with a named scope, SQL not my strong suit.

I would like to return ALL Machines which had it's LAST test fail.

My Machines model:

  has_many :lodged_tests, :dependent => :destroy
  has_one :last_test, :class_name => 'LodgedTest', :order => 'created_at DESC'

  named_scope :last_test_failed, :joins => :last_test, :conditions => [ "lodged_tests.is_passed = ?", false]

The named_scope does work except it returns Machines which have ANY failed tests. I need it to return machines which only the most recent(LAST) test failed.

Below is link to a quick diagram of what i'm trying to do.

Named Scope Diagram

any help would be great thanks.

Answer 1

I have figured out how to achieve the desired result using SQL, not very rails like but it gets the job done.

needed to include the limit of 1 in a subselect in the conditions.

named_scope :last_test_failed, :conditions => ["(SELECT is_passed FROM lodged_tests WHERE lodged_tests.machine_id = machines.id ORDER BY created_at DESC LIMIT 1) = ?", false]

If anyone knows how to do this query using rails scope functions, I would be very interested to see an example.

This related S.O. question put me on the right path: tricky named scope in RoR

Answer 2

Have you tried to add a limit of 1?

named_scope :last_test_failed, :joins => :last_test, :conditions => [ "lodged_tests.is_passed = ?", false], :order => "created_at desc", :limit => 1