Re: Select Distinct - Howto??

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

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.

In this sort of case, people generally use Perl:

	my %seen;
	my @address = grep !$seen{$_}++, map $_->address, 
		Photo->search(projno => $value);

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