Limit an array by the sum of a value within the records in rails3

So lets say I have the following in a Post model, each record has the field "num" with a random value of a number and a user_id.

So I make this:

@posts = Post.where(:user_id => 1)

Now lets say I want to limit my @posts array's records to have a sum of 50 or more in the num value (with onl开发者_如何学编程y the final record going over the limit). So it would be adding post.num + post2.num + post3.num etc, until it the total reaches at least 50.

Is there a way to do this?

I would say to just grab all of the records like you already are:

@posts = Post.where(:user_id => 1)

and then use Ruby to do the rest:

sum, i = 0, 0
until sum >= 50
  post = @posts[i].delete
  sum, i = sum+post.num, i+1
end

There's probably a more elegant way but this will work. It deletes posts in order until the sum has exceed or is equal to 50. Then @posts is left with the rest of the records. Hopefully I understood your question.

You need to use the PostgreSQL Window functions

This gives you the rows with the net sum lower than 50

SELECT a.id, sum(a.num) num_sum OVER (ORDER BY a.user_id) 
FROM   posts a
WHERE  a.user_id = 1 AND a.num_sum  < 50

But your case is trickier as you want to go over the limit by one row:

SELECT a.id, sum(a.num) num_sum OVER (ORDER BY a.user_id) 
FROM   posts a
WHERE  a.user_id = 1 AND a.num_sum  <= (
  SELECT MIN(c.num_sum) 
  FROM (
    SELECT sum(b.num) num_sum OVER (ORDER BY b.user_id) 
    FROM   posts b
    WHERE  b.user_id = 1 AND b.num_sum  >= 50
  ) c )

You have to convert this SQL to Arel.