On Tue, 21 May 2002 13:08:23 -0700 Stephen Keller <[EMAIL PROTECTED]>
wrote:
> 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.
Haruka and Michiru won't appreciate being separated.
Since you don't care about the ordering, a hash of hashes might work even
better.
use strict;
use warnings;
$dbh -> {RaiseError} = 1; # _ALWAYS_ check for errors
my $sth = $dbh -> prepare( "SELECT pid, name FROM pairings" );
$sth -> execute();
my ( $pid, $name, %hSenshi );
$sth -> bind_columns( \( $pid, $name ) );
while ( $sth -> fetch ) {
$hSenshi{$pid}{$name} = 1;
}
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
