RE: Select Distinct - Howto??

[prev] [thread] [next] [Date index for 2005/06/10]

Tony Bowden wrote:
> On Fri, Jun 10, 2005 at 02:12:15PM -0700, Mark Schoonover wrote:
>>     I'm rather new to CDBI, but am slowly getting my head around it.
>> Been through the archives, wiki and the docs, but I can't figure out
>> how to select distinct records. I know how to do it SQL wise, but
>> can't seem to make the leap with CDBI. add_constructor or
>> retrieve_from_sql doesn't look exactly like what I need. I'm looking
>> for the CDBI equivalent to 'select distinct address from photos
>> where projno=?'. 
> 
> There isn't really an equivalent, as Class::DBI is about returning
> objects rather than values.

	One of the things I'm trying to get my brain around, data versus
objects. 

> 
> In this sort of case, people generally use Perl:
> 
> 	my %seen;
> 	my @address = grep !$seen{$_}++, map $_->address,
> 		Photo->search(projno => $value);

	Given the fact that CDBI should just deal with objects, this looks
to be a better approach. Use CDBI to create objects, Perl to munge the data.

> 
> But you can also merrily write your own method to do this:
> 
> 	__PACKAGE__->set_sql(distinct_col_by_proj => <<'');
> 		SELECT DISTINCT(%s)
> 		FROM   __TABLE__
> 		WHERE  projno = ?
> 
> 	sub addresses_for_proj {
> 		my ($class, $projno) = @_;
> 		return
> 	$class->sql_distinct_col_by_proj('address')->select_col($projno); }
> 
> Tony

	Thanks Tony for the quick reply.

Mark Schoonover