Re: Notes on Join and what the Wiki says about it

[prev] [thread] [next] [Date index for 2004/07/05]

On Mon, Jul 05, 2004 at 03:17:54PM +0200, Rhesa Rozendaal wrote:
> I found the set_sql doesn't use the placeholder %s in
> 	set_sql( 'select __ESSENTIAL__ FROM __TABLE__ WHERE %s' );

Set-sql always uses those placeholders. Can you show what you were doing
that meant it didn't appear to be?

> sub search_my_albums {
>     my $class = shift;
>     my $sql = new SQL::Abstract;
>     my ($where, @bind) = $sql->where(@_);
>     my $phrase = $class->transform_sql(qq{
>         SELECT DISTINCT c.id
>         FROM   __TABLE(Music::CD=c)__
>         LEFT JOIN
>                __TABLE(Music::Artist=a)__
>         ON
>             __JOIN(c a)__
>         LEFT JOIN
>                __TABLE(Music::Track=t)__
>         ON
>             __JOIN(c t)__
>     }) . $where;

Eek. You should be declaring that SQL at the package level, rather than
inside your method, and you shouldn't really be calling transform_sql
yourself - Class::DBI will do that for you.

Class::DBI's JOIN in this case won't work for you if you're doing LEFT
JOINs.

> 	And is there a way to find out the join relations
> 	from the classes so this could be even more generic?

Yes - meta_info() will return a data structure giving you all these.

> Final questions:
> - I noted that I couldn't use __ESSENTIAL__, because it doesn't prefix 
> the fields with the table alias, leading to ambiguities. Is there an 
> easy way to get the primary column from this classes' table so that I 
> can put it in there?

$class->primary_column

ESSENTIAL(alias) will work in later versions.

> - Would there be a way to make this search more generic by building it 
> using the relationships? It would make a great addition to CDBI, 
> although I don't see a way to handle the table aliases transparently.

Have a look at how transform_sql handles the JOIN in Class::DBI itself.
It uses meta_info to build that all up itself.

Tony