Rails 3 - nested select and ".last"

Question

I have a scenario which seems to be should be something addressed on the model side of things but can't figure out a combination of Activerecord methods that works. The relevant parts of my db are as below:

Part:
id
part_id
created_at
...

Parttest:
id
part_id
pass (boolean)
created_at

A Part can have many Parttests and a Parttest belongs to a single part (setup in the relevant models as has_many and belongs_to).

In it's life - a part can be tested many times and can pass, fail, pass etc. Using rails / activerecord I would like to retrieve the recordset of Parts where the last (most recent) Parttest is a pass. Earlier records of parttest should not be taken into consideration and are for historical use only.

I've tried nested selects without much success. I could always code the logic in the controller to check each Part object in Part.all.each and push those into an array but this doesn't feel like the right way to do it. All help much appreciated :)

Answer 1

Edit: Misread your post, you want to do something like this reference

Part.joins(:parttests).where(parttests: {pass: true}) will give you all parts who have passing tests

try Part.joins(:parttests).last.where(parttests: {pass: true})

Answer 2

Using a scope

class Parttest
  scope :passed_tests, -> { where(pass: true) }
end

@part       = Part.find(1)                      # Fetch part instance
@part_tests = @part.parttests.passed_tests.last # Fetch its tests

Or using a condition on the association:

class Part
  has_many :passed_tests, class_name: 'Parttest', -> { where(pass: true) }
end

@part_tests = @part.passed_tests.last

Update

Now that I have understood the question correctly. How about this?

class Part
  has_many :passed_tests, class_name: 'Parttest', -> { where(pass: true) }
  scope :with_last_passed_test, -> { joins(:passed_tests).last }
end
Part.with_last_passed_test

Answer 3

I had originally posted a lengthy SQL query as one option, but that suggestion wasn't very clean so I've removed it. Here is a way to break this down into a few small pieces.

Say we start with these Parttest rows:

id  part_id  created_at  pass
1   1        2014-04-26  true
2   1        2014-04-27  false
3   2        2014-04-26  false
4   2        2014-04-27  true

1) First, we want to find the most recent Parttest row for each Part. We can do this with:

# Parttest model
def self.most_recent
  max_rows = select("part_id, MAX(created_at) as max_created_at").group(:part_id)
  joins("INNER JOIN (#{max_rows.to_sql}) AS max_rows
      ON parttests.part_id = max_rows.part_id
     AND parttests.created_at = max_rows.max_created_at")
end

The select will grab these values:

part_id  created_at
1        2014-04-27
2        2014-04-27

And the join will connect us back up with the other Parttest columns:

id  part_id  created_at  pass
2   1        2014-04-27  false
4   2        2014-04-27  true

2) Then we want to only keep the passing tests. We can do this with:

# Parttest model
scope :passed, -> { where(pass: true) }

Combining this with (1), we can get the most recent tests only if they were passing tests by calling:

Parttest.most_recent.passed

This would only include return this row from our example:

id  part_id  created_at  pass
4   2        2014-04-27  true

3) Then to find all Parts where the most recent test was passing we can provide this scope:

# Part model
scope :most_recent_test_passed, -> { where("id IN (?)", PartTest.most_recent.passed.map(&:part_id)) }