Hi Philip, you wrote:
> This is a programming/algorithm question: How would you store
> an unordered
> list of unordered lists in an RDBMS?
>
> +-----+----------------+
> | pid | name |
> +-----+----------------+
> | 1 | Rei |
> | 1 | Minako |
> | 1 | Michiru |
> | 2 | Usagi | -> (['Rei', 'Minako', 'Michiru'],
> | 2 | Rei | ['Usagi', 'Rei'],
> | 3 | Ami | ['Ami', 'Usagi'],
> | 3 | Usagi | ['Makoto', 'Minako', 'Haruka'])
> | 4 | Makoto |
> | 4 | Minako |
> | 4 | Haruka |
> +-----+----------------+
>
> The above is the database design that I'm currently using to
> accomplish this. I find that the Perl code to parse it into a Perl
> data structure is a pain, though:
If you really don't care about the output order of the final list,
try using a hash with the PID being the key and each element an array
reference. Then you just push each name onto the array associated with
the PID, as follows (you don't even need the order by clause with this
approach):
my $tHash = {};
my $sth = db::query("SELECT * FROM pairings WHERE fid = $fid");
while ( my $pairing = $sth->fetchrow_hashref) {
push ( @{$tHash->{ $pairing->{$pid} }}, $pairing->{name} );
}
@pairings = values(%$tHash);
In the case @pairings is a list of array references each of which contain
the names associated with the Pids. You've lost the relationship between
the names and the original pid, but you could always fetch that back out
of the $tHash if you needed it. Also eliminates the concatenation of the
slash separator in the mix.
Stph
