Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DBI, mysql, SELECT with JOIN and field names

by submersible_toaster (Chaplain)
on Feb 14, 2005 at 06:52 UTC ( #430707=perlquestion: print w/replies, xml ) Need Help??

submersible_toaster has asked for the wisdom of the Perl Monks concerning the following question:

Mellow Fonks,
It frustrates me that I cannot discover the solution to this problem and so risk annoying people posting what I don't think is strictly a DBI question.

For instance,  $sth->fetchall_arrayref({}) is something I have learned to love, except when returning results from joined tables where field names from different tables collide, clobbering keys in the hash. Apart from using AS in my SELECT statements, how can I convince DBI to return full table.column type field names. I thought that $dbh->{FetchHashKeyName} = might be my answer but it would seem not.

For example purposes only:

my $sth = $dbh->prepare( qq|SELECT requests.id, requests.name, request +s.requestor, requestors.name, requestors.email FROM requests LEFT JOIN requestors ON requests.requestor = requestor.id| ); my $list = $sth->fetchall_arrayref({});

Update:on Feb 14, 2005 at 21:36 GMT-10
The mapping field names to indexes idea, with my screwy logic below (untested).

my $sth = $dbh->prepare( qq|SELECT request.id, request.daterequested, request.requestor +, request.producer, requestor.name, requestor.email, producer.name, producer.e +mail LEFT JOIN requestor ON request.requestor = requestor.id LEFT JOIN producer ON request.producer = producer.id | ); my @mapping = qw/ request.id request.daterequested request.requestor r +equest.producer requestor.name requestor.email producer.name producer.email/ ; $sth->execute(); my $arrayref = $sth->fetchall_arrayref(); my @mapped = map { my %mapto; @mapto{@mapping} = @$_; \%mapto; } @$arrayref;


I can't believe it's not psellchecked

Replies are listed 'Best First'.
Re: DBI, mysql, SELECT with JOIN and field names
by dbwiz (Curate) on Feb 14, 2005 at 12:51 UTC

    The DBI docs as well as DBI Recipes warn aboout this problem.

    Since you are using MySQL, here is a MySQL-specific workaround. Take it as a basis for a customized function you may want to implement for your programs.

    my $query = qq{ select sometable.user, othertable.user from sometable inner join othertable using(some_id)}; my $sth = $dbh->prepare($query); $sth->execute; my @fields = map { $sth->{mysql_table}[$_] . "." . $sth->{NAME}[$_] } (0 .. $#{$sth->{NAME}} ) ; my @list; while ( my $row = $sth->fetchrow_arrayref() ) { my %rec = (); @rec{@fields} = @$row; push @list, \%rec; } use Data::Dumper; print Dumper \@list; __END__ $VAR1 = [ { 'sometable.user' => 'something', 'othertable.user' => 'somethingelse' }, { 'sometable.user' => 'something more', 'othertable.user' => 'somethingelse too' } ];

    HTH

      Lovely ++ , you are a dbwiz indeed. Your post has three lessons for me.

      • I have a much to learn about SQL
      • I must RTFM more carefully
      • One can sometimes be too lazy
      My many thanks.


      I can't believe it's not psellchecked
Re: DBI, mysql, SELECT with JOIN and field names
by ikegami (Pope) on Feb 14, 2005 at 08:24 UTC

    Not a direct solution to your problem, but you could do this:

    SELECT ..., requests.name AS request_name, ..., requestors.name AS requestor_name, ...

      Yes, this is the situation I am stuck with ATM, apart from making the sql messy(er), I bugs me that rather than table.column mapping directly into an HTML::Template <TMPL_VAR table.column> my choices are to either

      • use AS everywhere in my sql SELECTs
      • build a field map of field=>index for each select then use fetchall_array() without the {}. I dislike this approach only because I am lazy and must then change the mapping along with the select.(pedantic I know).
      • Find a lazy, more perlish way - which as yet eludes me

      I can't believe it's not psellchecked
        Just a simple idea (taking that your field names contain no underscores (for bravity, you can change this approach to fit your task)):
        my %AS = ( requests_id => 'requests.id AS requests_id', requests_name => 'requests.name AS requests_name', requests_rating => '(select count(*) from ratings where ....) AS requ +ests_rating', ...etc... ); my $sth = $dbh->prepare( qq|SELECT $AS{request_id}, $AS{request_name}, $AS{request_rating}, ...etc );

        This also allows expand your SELECTs in future without touching code logic.

        from DBIx documentation:

        NOTE: Because the query result is returned in a hash, there can only be one out of multiple fields with the same name fetched at once. If you specify multiple fields with same name, only one is returned from a query. Which one this actually is, depends on the DBD driver.

        so i don't think there is a more lazy perlish way yet... Are you going to provide it? :-)
Re: DBI, mysql, SELECT with JOIN and field names
by jbrugger (Parson) on Feb 14, 2005 at 09:08 UTC
    do you NEED the table.columname? else above idea would work, or either use fetchall_arrayref.
    my $ary = $sth->fetchall_arrayref; foreach my $row (@{$ary}) { $blah = @{$row}[0];
    etc.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://430707]
Front-paged by tye
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (14)
As of 2019-06-25 14:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Is there a future for codeless software?



    Results (107 votes). Check out past polls.

    Notices?