<div dir="ltr"><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">Hi all<br><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">I wonder if someone can help me: I have a table of "matches", under which there will be a has_many relationship with a number of "games" (relationship: "league_team_match_games"), which has two different types of game number: actual_game_number and scheduled_game_number; the scheduled_game_number forms part of the primary key,
though I don't see that that makes a difference, except perhaps that by
default the database engine will return columns in that order. When retrieving a given match, I'm doing something like the following to retrieve the games in the order they were played:<span style="font-family:monospace,monospace"><br><br></span><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><span style="font-family:monospace,monospace">my $match = $schema->resultset("LeagueTeamMatch")->find({</span><br><span style="font-family:monospace,monospace"> home_team => 33,</span><br><span style="font-family:monospace,monospace"> away_team => 43,</span><br><span style="font-family:monospace,monospace"> scheduled_date => "2014-09-17",</span><br><span style="font-family:monospace,monospace">}, {</span><br><span style="font-family:monospace,monospace"> prefetch => "league_team_match_games",</span><br><span style="font-family:monospace,monospace"> order_by => {</span><br><span style="font-family:monospace,monospace"> -asc => "league_team_match_games.actual_game_number",</span><br><span style="font-family:monospace,monospace"> },</span><br><span style="font-family:monospace,monospace">});</span><br><span style="font-family:monospace,monospace"></span></blockquote><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">I can then loop through with the following code:<br><br><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><span style="font-family:monospace,monospace"># Loop through games pre-update</span><br><span style="font-family:monospace,monospace">my $games = $match->league_team_match_games;</span><br><span style="font-family:monospace,monospace">while ( my $game = $games->next ) {</span><br><span style="font-family:monospace,monospace"> printf( "Actual: %d; scheduled: %d.\n", $game->actual_game_number, $game->scheduled_game_number );</span><br><span style="font-family:monospace,monospace">}</span><br></blockquote><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">This produces something like the below output, which is what I would expect:<br><br><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><span style="font-family:monospace,monospace">Actual: 1; scheduled: 2.</span><br><span style="font-family:monospace,monospace">Actual: 2; scheduled: 3.</span><br><span style="font-family:monospace,monospace">Actual: 3; scheduled: 1.</span><br><span style="font-family:monospace,monospace">Actual: 4; scheduled: 4.</span><br><span style="font-family:monospace,monospace">Actual: 5; scheduled: 5.</span><br><span style="font-family:monospace,monospace">Actual: 6; scheduled: 6.</span><br><span style="font-family:monospace,monospace">Actual: 7; scheduled: 7.</span><br><span style="font-family:monospace,monospace">Actual: 8; scheduled: 8.</span><br><span style="font-family:monospace,monospace">Actual: 9; scheduled: 9.</span><br><span style="font-family:monospace,monospace">Actual: 10; scheduled: 10.</span><br></blockquote><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">However, if I perform an update on the $match object prior to looping through the games:<br><br><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><span style="font-family:monospace,monospace">my $dt_updated = DateTime->now;</span><br><span style="font-family:monospace,monospace">$match->update({</span><br><span style="font-family:monospace,monospace"> updated_since => sprintf( "%s %s", $dt_updated->ymd, $dt_updated->hms ),</span><br><span style="font-family:monospace,monospace">});</span><br></blockquote><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">...the sort order is going by scheduled_game_number, not actual_game_number as specified in the original query:<br><br><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex" class="gmail_quote"><span style="font-family:monospace,monospace">Actual: 3; scheduled: 1.</span><br><span style="font-family:monospace,monospace">Actual: 1; scheduled: 2.</span><br><span style="font-family:monospace,monospace">Actual: 2; scheduled: 3.</span><br><span style="font-family:monospace,monospace">Actual: 4; scheduled: 4.</span><br><span style="font-family:monospace,monospace">Actual: 5; scheduled: 5.</span><br><span style="font-family:monospace,monospace">Actual: 6; scheduled: 6.</span><br><span style="font-family:monospace,monospace">Actual: 7; scheduled: 7.</span><br><span style="font-family:monospace,monospace">Actual: 8; scheduled: 8.</span><br><span style="font-family:monospace,monospace">Actual: 9; scheduled: 9.</span><br><span style="font-family:monospace,monospace">Actual: 10; scheduled: 10.</span><br></blockquote><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">Does anyone know why this would be? I've checked what I think would be the relevant parts of the documentation and can't see anything - though it's entirely possible, if not probable that I'm being completely stupid. The DB is MySQL if that makes a difference.<br><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">Many thanks in advance.<br><br><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,102)">Chris<br></div></div>