Single row selects with Class::DBI

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

This is my first experience making significant use of Class::DBI. I've run into something that seems like it should be simple, but I can't find a (simple) way to do it. I've read the docs, the source, searched the monastery, and looked at http://www.class-dbi.com, and this is all I've been able to come up with.

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?

So.... I keep feeling I'm missing something. How do I tell Class::DBI to build a method that returns only a single row?

/me braces for embarassingly obvious answer....

Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.

Comment on Single row selects with Class::DBI Select or Download Code

Replies are listed 'Best First'.
Re: Single row selects with Class::DBI by herveus (Prior) on Jan 25, 2005 at 18:27 UTC
Howdy! May I recommend the 'construct' method? Turn `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] into `sub my_beta_from_alpha { my ($class, %args) = @_; my $sth = $class->sql_by_alpha(@args{qw(bloop glank)}); $sth->execute; my $result = $class->construct($sth->fetchrow_hashref); return $result; }` [download] yours, Michael	[reply] [d/l] [select]
Re^2: Single row selects with Class::DBI by VSarkiss (Monsignor) on Jan 25, 2005 at 19:09 UTC
I'd read about the `construct` method, but didn't think to use it here. I can see how that would work. Still, it seems like a lot of work to go thru, when what I want is a simpler version of the `set_sql`/`search_by_` stuff. I was hoping for something as lazy as those.... Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l] [select]
Re: Single row selects with Class::DBI by perrin (Chancellor) on Jan 25, 2005 at 19:13 UTC
The simplest way is this: `(my $foomp) = My::DBI::beta->search_by_alpha( ... );` [download]	[reply] [d/l]