Here's a simplified example. Say I have two tables, alpha and beta. Both of these are represented by subclasses of Class::DBI, let's say My::DBI::alpha and My::DBI:beta. Suppose that by the key relationships, I'm guaranteed that the following will return a single row from beta:

my $sql = q{
SELECT
      b.shpritz
    , b.fweep
    , b.sproing
FROM
    beta b
    INNER JOIN alpha a ON b.beta_id = a.beta_id
WHERE
        a.bloop = ?
    AND a.glank = ?
};
[download]

(Thanks to BooK's Acme::MetaSyntactic for the column and variable names. :-)

I'd like to install a method in My::DBI::beta that knows to return the single row. If I use the set_sql call: My::DBI::beta->set_sql(by_alpha => $sql);It will create a search_by_alpha method in the class, that in list context will return an array of My::DBI::beta objects, or in scalar context will return an iterator. What I want is a method that will return a single object, like this: my $skreek = My::DBI::beta->MAGIC_by_alpha(bloop => 1, glank => 2);As it stands, I have to do something like: my $glink = (My::DBI::beta->search_by_alpha( ... ))[0];or my $foomp = My::DBI::beta->search_by_alpha( ... )->next;Both of which look ... well, wrong.

I can't use the retrieve or retrieve_from_sql or sql_single methods because the underlying SQL statements assume I'm selecting from a single table, not a join of two (or more) tables. Alternatively, if I use the sql_by_alpha method installed by the underlying Ima::DBI class, I can cook up my own method that returns the values:

sub my_beta_from_alpha
{
    my ($class, %args) = @_;
    my $sth = $class->sql_by_alpha(@args{qw(bloop glank)});
    $sth->execute;
    my $result = $sth->fetchrow_arrayref;
    return @$result;
}
[download]

But how do I return the object, not just the column values?