Notes on Join and what the Wiki says about it

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

Hi all,

I've been playing with joins lately, because I needed a search that 
covered multiple tables. There is some info on it on the Wiki, but 
either I didn't understand what was going on, or it is that that code is 
untested.

I found the set_sql doesn't use the placeholder %s in
	set_sql( 'select __ESSENTIAL__ FROM __TABLE__ WHERE %s' );
(See http://www.class-dbi.com/cgi-bin/wiki/index.cgi?UsingJoins)

The code on
http://www.class-dbi.com/cgi-bin/wiki/index.cgi?DirectlyExecuteSql
doesn't work for me because of the
	$class->create_where
and the last line has a typo where it references $self (should be $class).

I wanted to run this by you guys before messing up the contents of the 
Wiki, so please comment on my code.

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;
     my $dbh = $class->db_Main();
     my $sth = $dbh->prepare_cached($phrase);
     $sth->execute(@bind);
     return $class->sth_to_objects($sth);
}

This is a CD search on fields from Artist, Track and CD.
It requires known aliases Artist=a, Track=t and CD=c.
It also offers all the flexibility from SQL::Abstract's where().

	Side question:
	Is there a way to make this more transparent,
	so I don't have to know the aliases in my
	userspace code?
	And is there a way to find out the join relations
	from the classes so this could be even more generic?

You could for instance do

Music::CD->search_my_albums( {
	c.year => {between => [1980, 1985]},
	t.title => {like => '%love%'}
	});

to find all the love songs between 1980 and 1985 ;^)

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?
- 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.

Thank you for your attention :-)

Rhesa Rozendaal