Re: Feature request - Allow searching columns of related tables in search() and search_like()

[prev] [thread] [next] [Date index for 2005/01/11]

------=_Part_8_23914978.1105419870624
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Tue, 11 Jan 2005 00:46:04 +0000, Tony Bowden <tony-cdbitalk@xxxxx.xxx> wrote:
> On Mon, Jan 10, 2005 at 11:12:13PM +0000, Tony Bowden wrote:
> > IMO the key is just coming up with the syntax that links the various tables
> > together.
> 
> If anyone wants to hack on it, the basics are below.

Nice.  I had a little play with this, and it looks like it could be
handy (it doesn't quite go as far as I was hoping, but it looks very
promising).

I had to patch Class::DBI to get it to work though.  The __ESSENTIAL__
columns were being pulled from the wrong table.  I setup a test
environment using Artist, CD and Track as examples, and then setup the
relationship like this:

Music::Artist->manymany(tracks => ['Music::CD', 'Music::Track']);

This created a set_sql statement that looks like this:

  SELECT __ESSENTIAL(track)__
     FROM __TABLE(Music::Artist=artist)__,
               __TABLE(Music::CD=cd)__,
               __TABLE(Music::Track=track)__
   WHERE __JOIN(artist cd track)__
      AND artist = ?

Which looks right.  But then calling  $artist->tracks generated the
following SQL statement:

   SELECT track.artistid
     FROM artist AS artist, cd AS cd, track AS track
    WHERE  track.cd = cd.cdid  AND  cd.artist = artist.artistid
      AND artist = ?

The first line is incorrect, artistid is the primary key from the
Artist table, not the Track table.  The attached patch to Class::DBI
adds classname support to the __ESSENTIAL__ call, namely allowing
__ESSENTIAL(Music::Track=track)__.  You then have to get the
ManyMany.pm module to generate the __ESSENTIAL__ string with this new
format to get it to work properly (there might be a better way to fix
this but it worked for me).

I'll play around a bit more to see what else can be done with this.

Cheers,

        -- 
        Cees Hek

------=_Part_8_23914978.1105419870624
Content-Type: text/x-patch; name="CDBI_essentials.patch"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment; filename="CDBI_essentials.patch"

--- DBI.pm.orig=092005-01-10 23:29:57.000000000 -0500
+++ DBI.pm=092005-01-10 23:54:17.000000000 -0500
@@ -210,6 +210,8 @@
 =09$sql =3D~ s/__JOIN\((.*?)\)__/$expand_join->($1)/eg;
 =09$sql =3D~ s/__ESSENTIAL__/join ", ", $self->_essential/eg;
 =09$sql =3D~
+=09=09s/__ESSENTIAL\((.*?)=3D(.*?)\)__/join ", ", map "$2.$_", $1->_essent=
ial/eg;
+=09$sql =3D~
 =09=09s/__ESSENTIAL\((.*?)\)__/join ", ", map "$1.$_", $self->_essential/e=
g;
 =09if ($sql =3D~ /__IDENTIFIER__/) {
 =09=09my $key_sql =3D join " AND ", map "$_=3D?", $self->primary_columns;

------=_Part_8_23914978.1105419870624--