Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: Re-indexing a SQL resultset by id

by Juerd (Abbot)
on Jul 09, 2011 at 22:28 UTC ( #913553=note: print w/ replies, xml ) Need Help??


in reply to Re-indexing a SQL resultset by id

It seems to me that your database design (or the name of the 'id' column) is broken if you have non-unique ID's.

As for supporting this in the module: I'm not inclined to support something in DBIx::Simple that makes the value type returned dependent on the contents of the result set. That results in rather fragile code. You could just add a method, though:

# untested package DBIx::Simple::Result::Metaperl; our @ISA = qw(DBIx::Simple::Result); sub map_arrays_of_hashes { my ($self, $column_name) = @_; my %arrays; for my $row ($self->hashes) { push @{ $arrays{$row->{id}} }, $row; } return wantarray ? %arrays : \%arrays; } ... $db->result_class = 'DBIx::Simple::Result::Metaperl'; my %arrays_of_hashes = $db->query(...)->map_arrays_of_hashes('id');

To keep DBIx::Simple simple, I'm not adding a proliferation of variants; a vast number map_foos_of_bars permutations could be thought of and I'm sure there's they could all be useful in someone's code somewhere, but let's draw a line... :)

Juerd


Comment on Re: Re-indexing a SQL resultset by id
Download Code
Re^2: Re-indexing a SQL resultset by id
by metaperl (Curate) on Jul 09, 2011 at 23:36 UTC
    It seems to me that your database design (or the name of the 'id' column) is broken if you have non-unique ID's.
    FALSE:
    CREATE TABLE students id PRIMARY KEY SERIAL firstname TEXT lastname TEXT ; CREATE TABLE classes id PRIMARY KEY SERIAL name text ; CREATE TABLE student_classes studentid INT classid INT ;
    and now:
    SELECT * FROM student_classes;
    Will you get several instances of each id? Yes? Is the database design un-normalized? No. Conclusion. There are reasonable queries leading to multiple instances of certain fields by which one might want to collate results. In this case, I might want a hash keyed by student id and an array ref of all results with that student id. Call it "collation".



    The mantra of every experienced web application developer is the same: thou shalt separate business logic from display. Ironically, almost all template engines allow violation of this separation principle, which is the very impetus for HTML template engine development.

    -- Terence Parr, "Enforcing Strict Model View Separation in Template Engines"

      SELECT * FROM student_classes;
      Will you get several instances of each id? Yes?

      Yes, but these fields are not called 'id'; here, it's just a suffix. A join, however, could result in multiple rows of the result set with the same id, even in a nice database design, so I do retract my observation about the database perhaps not being well-designed.

      Juerd

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://913553]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2015-07-03 13:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (53 votes), past polls