Re: Request: Support for __JOIN(c1.fkey c2)__

[prev] [thread] [next] [Date index for 2005/05/05]

Quoting Tony Bowden :

> Thanks for this. In general I'm happy with the concept, but I'm not
> completely following the syntax. Could you take a stab at some docs
> and/or tests that show some of this in action?

Are you talking about the syntax in the perl code or the syntax in the 
__JOIN__?
  I'd love to expand the documentation on the __JOIN__, but AFAIK there ain't
none, right?

A more specific example for this can be seen in your test classes Film and
Director.  You should be able to add the following relationships to Film:

package Film;
Film->has_a(Director => 'Director');
Film->has_a(CoDirector => 'Director');

Now let's say I want to have a query for films whose director's birthday is
before a certain date.  I may be tempted to specify the following query:

Film->set_sql(directors_born_before => <<'');
    SELECT __ESSENTIAL(f)__
    FROM __TABLE(=f)__, __TABLE(Director=d)__
    WHERE __JOIN(f d)__ AND d.Birthday <= ?

However, since Film has two "has_a" relationships to the Director class, the
__JOIN__ is ambiguous and will be expanded into either "f.director = 
d.name" or
"f.codirector = d.name" (depending on how perl sorts the 
meta_info('has_a') hash
keys).

My code enhances the __JOIN__ syntax to allow you to specify which 
exact foreign
key to use in the join.  So, you'd specify "__JOIN(f.Director d)__" to get the
director (and, conversely, you'd specify "__JOIN(f.CoDirector d)__" if you
wanted to get the CoDirector):

Film->set_sql(directors_born_before => <<'');
    SELECT __ESSENTIAL(f)__
    FROM __TABLE(Film f)__, __TABLE(Director d)__
    WHERE __JOIN(f.Director d)__ AND d.Birthday <= ?

Film->set_sql(codirectors_born_before => <<'');
    SELECT __ESSENTIAL(f)__
    FROM __TABLE(Film f)__, __TABLE(Director d)__
    WHERE __JOIN(f.CoDirector d)__ AND d.Birthday <= ?

As far as testing goes, you could add the following to 19-set_sql.t:

{    # join with fkey
	Actor->has_a(film => "Film");
	Film->set_sql(
		ratename => qq{
		SELECT __ESSENTIAL(f)__
		FROM   __TABLE(=f)__, __TABLE(Actor=a)__
		WHERE  __JOIN(a.film f)__
		AND    f.rating = ?
		AND    a.name LIKE ?
		ORDER BY title
	}
	);

	my @apg = Film->search_ratename(PG => "A_");
	is @apg, 2, "2 Films with A* that are PG";
	is $apg[0]->title, "A", "A";
	is $apg[1]->title, "B", "and B";
}

BTW, I think that transform_sql should test for and complain if it's 
expanding a
has_a relationship that's ambiguous (i.e. "__JOIN(f d)__" in my example).  My
code doesn't do that check.

Also, a similar problem exists when specifying has_many relationships:

     Director->has_many( DirectedFilms => 'Film');
     Director->has_many( CoDirectedFilms => 'Film');

Again, when ambiguous, you should be able to specify the foreign key and CDBI
should croak on ambiguities:

     Director->has_many( DirectedFilms => 'Film.Director');
     Director->has_many( CoDirectedFilms => 'Film.CoDirector');

Anyway, I'm using the new __JOIN__ syntax in an enhanced AbstractSearch where
I'll be able to specify where clauses from joined tables ("Films whose title
contains $film_title and whose director's insanity equals $director_nuts and
whose codirector's instanity equals $codirector_nuts") like so:

my @films = Film->search_where( {
       'title' => { -like => "%$film_title%" },
       'director.IsInsane' => $director_nuts,
       'codirector.IsInsane' => $codirector_nuts,
} );

Which will be converted to the following SQL before CDBI has its way with it:

SELECT
    __ESSENTIAL(t_film)
FROM
    __TABLE(=t_film)__,
    __TABLE(Director=t_film_director)__,
    __TABLE(Director=t_film_codirector)__
WHERE
       __JOIN(t_film.Director t_film_director)__ AND
       __JOIN(t_film.CoDirector t_film_codirector)__ AND
       title LIKE '%voter_name%' AND
       t_film_director.isnuts = ? AND
       t_film_codirector.isnuts = ?

     - Stepan