How can I calculate successive elements in a database using Rails?

Question

Is there a way I can query the database to return only the elements that have happened successively (in time) according to a set variable?

Specifically, I need to find out how many times the user has won a game in a row. The games are stored in the database with an attribute win_loss (1 is win, 0 is loss).

I would like to see if the user has won 3 games in a row. Is there a way to do this in the database?

If not, what would it look like in the application?

I apologize ahead of time if this is confusing. Please ask questions and I will try to clear it up. I'm using Ruby on Rails.

Answer 1

I would take different approach. I would add a column consecutive_wins into your User model (negative numbers could represent consecutive losses if you need that as well).

EDIT: if you really need to query it...

Two queries:

1. select the newest victory and newest loose for given user (could be done in one query, in SQL SELECT max(created_at) AS newest, win_loss FROM games GROUP BY win_loss (I can't recall how to do grouping functions in Rails right now but you should get the idea)
2. and then, regarding the result of query (1):
   1. if both newest win and newest lose are found, count the games with condition that created_at > newest_win AND created_at > newest_lose
   2. if only newest win or lose found, user was always wining or always loosing, just count the number of his games
   3. in other case there were no games

I recommend adding the column to user model, it will be better when you need to display the whole table of gamers and their consecutive wins/loses.