Re: DBI, mysql, SELECT with JOIN and field names

[ikegami]

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

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

[submersible_toaster]

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

[maard]

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.

[jbrugger]

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? :-)