[Dbix-class] Update screws up order_by on has_many prefetched results

[Chris Welch]

Hi all

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:

my $match = $schema->resultset("LeagueTeamMatch")->find({
>   home_team => 33,
>   away_team => 43,
>   scheduled_date => "2014-09-17",
> }, {
>   prefetch => "league_team_match_games",
>   order_by => {
>     -asc => "league_team_match_games.actual_game_number",
>   },
> });
>

I can then loop through with the following code:

# Loop through games pre-update
> my $games = $match->league_team_match_games;
> while ( my $game = $games->next ) {
>   printf( "Actual: %d; scheduled: %d.\n", $game->actual_game_number,
> $game->scheduled_game_number );
> }
>

This produces something like the below output, which is what I would expect:

Actual: 1; scheduled: 2.
> Actual: 2; scheduled: 3.
> Actual: 3; scheduled: 1.
> Actual: 4; scheduled: 4.
> Actual: 5; scheduled: 5.
> Actual: 6; scheduled: 6.
> Actual: 7; scheduled: 7.
> Actual: 8; scheduled: 8.
> Actual: 9; scheduled: 9.
> Actual: 10; scheduled: 10.
>

However, if I perform an update on the $match object prior to looping
through the games:

my $dt_updated = DateTime->now;
> $match->update({
>   updated_since => sprintf( "%s %s", $dt_updated->ymd, $dt_updated->hms ),
> });
>

...the sort order is going by scheduled_game_number, not actual_game_number
as specified in the original query:

Actual: 3; scheduled: 1.
> Actual: 1; scheduled: 2.
> Actual: 2; scheduled: 3.
> Actual: 4; scheduled: 4.
> Actual: 5; scheduled: 5.
> Actual: 6; scheduled: 6.
> Actual: 7; scheduled: 7.
> Actual: 8; scheduled: 8.
> Actual: 9; scheduled: 9.
> Actual: 10; scheduled: 10.
>

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.

Many thanks in advance.


Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20150820/f4fa10e3/attachment.htm>