update: I think you're confused hardburn. I said look at the "Ima::DBI queries" portion of the manual, not use "retrieve_from_sql".

That will still return a list, though, regardless of wheather there was only one result returned or not. So in this case, there's little advantage ot use retrieve_from_sql over a normal search().

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

:(){ :|:&};:

Note: All code is untested, unless otherwise stated

I haven't used Class::DBI for a lot of projects, but I've been looking for such a method myself and haven't found one. Here's how I normally handle it, assuming username is a unique column that isn't the primary key:

my ($username) = My::DBI::Users->search( username => 'foo' );
# Could also do:
my $username = (My::DBI::Users->search( username => 'foo' ))[0];
# Or maybe (haven't tried this one)
my $username = My::DBI::Users->search( username => 'foo' ) 
    ->next();
[download]

In the first example, search will generate a list, the first element of which we save to $username and anything else is thrown away. Since we know this is a unique field, there should only be one result, so nothing is really lost.

In the second, we directly specify the first element.

In the third, search is being called in scalar context, so it will return an iterator. We immediatly grab first result and throw the iterator away. This one will probably fail with errors like "calling method on undefined value" if there were no results.

I usually use the first method.

Thanks for the helpful replies so far. I think that maybe I wasn't entirely clear, however. I was thinking of returning a list of distinct objects:

table units:
name      location
------------------
foo_unit  1
foo_unit  2
bar_unit  1
baz_unit  3
[download]

I was hoping to find something like:

$iterator = Package::units->search_distinct(name);
while ($iterator->next) { print };

foo
bar
baz
[download]

While the first suggestion will allow me to do this (thank you), I was hoping that I could find a way to do it without hardcoding each individual query into the class.

Regarding the schema: It is part of an existing project that cannot (easily) be changed.

Neither. I was hoping that there was a Class::DBI mechanism that would return only 'distinct' values without having to either:

a) filter the results of a Package->retrieve_all;

-or-

b) write a

 
Foo->set_sql(distinct => 'SELECT DISTINCT...')
[download]

...for any class that needs this functionality. I hope I am being clear this time. -Thanks

I think the consensus is that there isn't a distinct() method. Why don't you write one and contribute back?

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.