How do I select the max child value in ActiveRecord?

Question

I'm not even sure how to word this, so an example:

I have two models,

Chicken   
  id   
  name

EggCounterReadings
  id   
  chicken_id   
  value_on_counter
  timestamp

I don't always record a count for every chicken when I do counts.

Using ActiveRecord how do I get the latest egg count per chicken?

So if I have 1 chicken and 3 counts, the counts would be 1 today, 15 tomorrow, and 18 the next day. That chicken has laid 18 eggs, not 34

UPDATE: Found exactly what I was trying to do in MySQL. Find "The Rows Holding the Group-wise Maximum of a Certain Column". So I need to .find_by_sql("SELECT * FROM (SELECT * FROM EggCounterReadings WHERE <conditions> ORDER BY timestamp DESC) GROUP BY chicken_id")

This is VASTLY simplified, but it's the best I could do to explain what I'm looking for. — hometoast, Nov 17 '10 at 19:43
Almost duplicate: http://stackoverflow.com/questions/185569/fetching-minimum-maximum-for-each-group-in-activerecord — hometoast, Nov 18 '10 at 14:33

Brian · Accepted Answer · 2010-11-18T00:08:47.840

1

Given your updated question, I've changed my answer.

chicken = Chicken.first
count = chicken.egg_counter_readings.last.value_on_counter

If you don't want the latest record, but the largest egg yield, then try this:

chicken = Chicken.first
count = chicken.egg_counter_readings.maximum(value_on_counter)

I believe that should do what you want.

edited Nov 18 '10 at 00:08

answered Nov 17 '10 at 21:57

Brian

6,820
3
29
27

Thanks. I do however record more than 1 item per entry. Think of it this way, chickens are happily laying eggs all day, a counter below them. I come along and read the counter for each chicken and write that number down. Perhaps I could rename the second model to LayedEggCount. – hometoast Nov 17 '10 at 22:11
That looks good. Perhaps I'm trying to over optimize before I have a problem. But I'll have lots of chickens to iterate over later. Is there any way to get a collection of the max egg counter readings grouped by chicken? What I would do in SQL is SELECT MAX(value_on_counter) as value_on_counter, id, rest,of,fields group by chicken_id. – hometoast Nov 18 '10 at 00:24
And with that I think I have my answer: EggCounterReadings.find_by_sql("insert awesome statement from above"). Accepted answer as that's 98% of what I needed! – hometoast Nov 18 '10 at 00:25
1

A more ActiveRecord-ish approach might look more like this: `EggCounterReading.maximum(value_on_counter, :group => :chicken)`. But find_by_sql will most likely be faster. – Brian Nov 18 '10 at 00:47

How do I select the max child value in ActiveRecord?

1 Answers1