Added follow-up artile, Ideas on utilizing Teng#new_row_from_hash
Premise
Lately I switched my O/R mapper from Data::ObjectDriver to Teng and I really enjoy its lightness.Some O/R mappers provide so many functionalities and, hence, they are heavy and hard to track source codes. DOD's transparent caching was tricky, for instance. Despite the fact that they are functionality-rich, when we try to write our own SQL statement, we have to take DB handle out of them and do the rest by ourselves. Then we can't make use of O/R mapper's result object, any more.
Teng, on the other hand, works nicely as O/R mapper and DBI wrapper. Execute() wraps DBI and it handles the creation of statement handle and its execution. By the way dbi(), which is called in execute(), takes care of process id check and reconnection so it's folk-safe. Other O/R mapper-ish methods for CRUD including search_by_sql() calls execute() and my favorite part is that search_by_sql() still creates Teng::Iterator object so we can use Teng::Row's functionalities with our own SQL statements.
my $itr = $teng->search_by_sql(q{
SELECT service.*
FROM service
LEFT JOIN service_ranking
ON service.id = service_ranking.service_id
ORDER BY service_ranking.rank IS NULL service_ranking.rank ASC
}, [], 'service');
# $itr is-a Teng::Iterator
My Problem and Workaround
Sometimes, when retrieving column values from 2 or more tables, I get confused which table name I should set on 3rd argument. Since retrieved values don't represent any particular object or table, it's natural that there is no corresponding table row class. In such case, I called $teng->execute() and tried to create table row objects for each table. The code was something like below.my $sth = $teng->execute(q{
SELECT user.name, user_ranking.rank, user_ranking.fluctuation
FROM user
LEFT JOIN user_ranking
ON user.id = user_ranking.user_id
ORDER BY user_ranking.rank IS NULL user_ranking.rank ASC
}, []);
my $user_row_class = $teng->schema->get_table('user')->{row_class};
my $ranking_row_class = $teng->schema->get_table('user_ranking')->{row_class};
my @ranked_users;
while (my $hashref = $sth->fetchrow_hashref) {
my $user_row = $user_row_class->new(+{
row_data => +{
id => $hashref->{user_id},
name => $hashref->{name},
},
table_name => 'user',
teng => $teng,
});
my $ranking = $ranking_row_class->new(+{
row_data => +{
user_id => $hashref->{user_id},
rank => $hashref->{rank},
fluctuation => $hashref->{fluctuation},
},
table_name => 'user_ranking',
teng => $teng,
});
# do something with MyApp::DB::Row::User and MyApp::DB::Row::UserRanking objects
push @ranked_users, +{
name => $user->name,
rank => $ranking->rank,
fluctuation => $ranking->fluctuation_str, # fluctuation_str() returns stringified fluctuation: UP, STAY and DOWN
};
}
Solution
The previous code worked O.K. to me, but this process was somewhat complex and I didn't really know what arguments I should specify on $row_class->new(). So I looked for some workaround and what I found was this. This $teng->data2row() allows me to do what I did above and, additionally, it sets dummy query as $row->{sql} so it should be easier to debug especially when get_column() doesn't work and the message "Specified column 'some_nongiven_column' not found in row (query: ..... )" is given on croak(). This method, however, is not merged to master branch, yet.I'm more than happy to have this method in the near future.
