Last Updated: February 25, 2016
·
1.038K
· georgiee

Lottery Drawing SQL, Rails scope :)

I created the following ActiveRecord scope in my latest app. Works like a charme :) You can find all matches of a range (4-6) or an exact match. There will be a field "matches" on each lottery ticker record found. I love ActiveRecord!

class LotteryTicket
    has_many :lottery_ticket_numbers

    scope :matching, ->(numbers, min_matches = 3, max_matches = 6) {
        select("lottery_tickets.id,
          COUNT(lottery_ticket_numbers.number) AS matches")
        .joins(:numbers)
        .where("lottery_ticket_numbers.index, lottery_ticket_numbers.number)
          IN ((0, ?), (1,?), (2,?), (3, ?), (4, ?), (5, ?)", *numbers)
        .group("lottery_tickets.id")
        .having("COUNT(lottery_ticket_numbers.number) >= ?", min_matches)
        .having("COUNT(lottery_ticket_numbers.number) <= ?", max_matches)
      }
end

Usage

LotteryTicket.matching(4,6).each do |ticket|
  puts "a winning ticket found with #{ticket.matches} matches}
}

DB schema

LotteryTicket
  id

LotteryTicketNumber
  id
  lottery_ticket_id
  number
  index

Have a fresh tip? Share with Coderwall community!

Post
Post a tip