3

I have some sporting results that i'd like to loop through and group under dates in a table. For example;

|Sat, 20|
|Game 1 Results|
|Game 2 Results|
|Sun, 21|
|Game 3 Results|
|Sat, 27|
|Game 4 Results|

My Controller

@matches = Match.group([:matchId, 'DATE(matchDate)']).inject([]) do |results, matches|
           team_names = Match.where(matchId: matches.matchId)
           results << [matches.id, matches.matchDate, team_names.first.team.name, team_names.last.team.name, team_names.first.points, team_names.last.points]
end

At the moment i'm doing a basic loop, which is showing.

|Sat, 20|
|Game 1 Results|
|Sat, 20|
|Game 2 Results|
|Sun, 21|
|Game 3 Results|
|Sat, 27|
|Game 4 Results|
DollarChills
  • 1,076
  • 1
  • 15
  • 33

2 Answers2

2

SQL GROUP BY is for aggregating values: you can get the count of matches per date, or the sum of goals, and so on. (Read more here.) But if you want a list of all games in groups, it's not a good fit.

You're seeing the same date twice because you're grouping by the date and the match ID.

If you don't have a lot of data, you could do it all in Ruby code:

Match.all.group_by { |match| match.matchDate.to_date }

This gives you a hash with dates as keys and lists of matches as values.

This answer assumes that matchDate is a time, because you used DATE(matchDate) in your example. If it's already a date, you don't need the .to_date bit above.

Also note that DATE(a_time_column) in SQL will extract the date in the database time zone. Rails often has the database configured to use the UTC time zone internally. So if you do go with a database query like that, be aware of this and make sure you get the right date.

Community
  • 1
  • 1
Henrik N
  • 15,786
  • 5
  • 82
  • 131
1

The problem is that you are adding the matchId to the group clause. Another option is to find all distinct dates and iterate over them to show your table. I assume that matchDate is a Datetime, if not it could be cleaner.

distinct_dates = Match.pluck('DATE(matchDate)').uniq.compact
distinct_dates.each do |date|
  date = DateTime.parse(date_string)
  roles = Role.where( :created_at => (date)..(date + 1.day) )
  p date
  p roles.map(&:matchId)
end
vitomd
  • 806
  • 7
  • 14