cheshire137 / competiwatch

Web app to track and visualize your competitive match history in Overwatch.
MIT License
17 stars 4 forks source link

Store group members in matches table #71

Closed cheshire137 closed 6 years ago

cheshire137 commented 6 years ago

Fixes #62.

This will help keep row counts down: I can log a match where I played with 4 friends and only have 1 row created instead of 5, assuming friends records already exist. It will also improve query performance when trying to load all group members for all matches in a season.

cheshire137 commented 6 years ago

To compare before/after output locally:

psql competiwatch_development -c "SELECT match_id, ARRAY_AGG(friend_id ORDER BY friend_id) AS friend_ids FROM match_friends GROUP BY match_id ORDER BY match_id;" > before-local.txt
rake db:migrate
psql competiwatch_development -c "SELECT id AS match_id, group_member_ids AS friend_ids FROM matches WHERE group_member_ids <> '{}' ORDER BY match_id;" > after-local.txt
diff before-local.txt after-local.txt
cheshire137 commented 6 years ago

Similar results for production:

heroku pg:psql -c "SELECT match_id, ARRAY_AGG(friend_id ORDER BY friend_id) AS friend_ids FROM match_friends GROUP BY match_id ORDER BY match_id;" > before-prod.txt
heroku run rake db:migrate
heroku pg:psql -c "SELECT id AS match_id, group_member_ids AS friend_ids FROM matches WHERE group_member_ids <> '{}' ORDER BY match_id;" > after-prod.txt
diff before-prod.txt after-prod.txt