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

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

Tony Bowden wrote:

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

I tried this:
__PACKAGE__->set_sql( all_features => qq{
     SELECT __ESSENTIAL__
     FROM __TABLE__
     WHERE %s
});

and then call it with
	MDB->search_all_features( field => 'value');

Apparently I misread the docs somewhere, because this generates an SQL 
statement like:
	select f1,f2 from mdb where
where I expected at least
	select f1,f2 from mdb where field=?

The example from the manual says this:

Music::CD->set_sql(new_music => qq{
                 SELECT __ESSENTIAL__
                   FROM __TABLE__
                  WHERE year > ?
});
sub search_new_music {
         my ($class, @args) = @_;
         my $sth = $class->sql_new_music;
         $sth->execute(@args);
         return $class->sth_to_objects($sth);
}

Clearly it passes the arguments as bind values to the statement, so it 
does _not_ use the %s.

So let's try to call it like this:
	MDB->sql_all_features( field => 'value' );

This generates this SQL:
	select f1,f2 from mdb where field

So the sql_ only accepts one parameter (because it doesn't do anything 
with the 'value' parameter), and it must be a scalar as well.
Hmmm.
Ah, but it returns a DBIx::ContextualFetch::st!
Well, that makes things different, doesn't it?
It means I should do it like this:

__PACKAGE__->set_sql( my_albums => 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)__
	%s
});

sub query_my_album {
     my $class = shift;
     my $sql = new SQL::Abstract;
     my ($where, @bind) = $sql->where(@_);
     return $class->sth_to_objects(
		$class->sql_my_albums($where),
		\@bind
	);
}

LOL! Now it looks the same as on the Wiki, except for the use of 
SQL::Abstract. Maybe I should have thought this through a bit more 
before posting... (but then I wouldn't have had your comments)

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

It seems it does though! The above works fine, as far as I can tell.

> $class->primary_column

Of course... I should have known that :-)

> ESSENTIAL(alias) will work in later versions.

That will make it even more transparent.

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

I'm going to take a good look at this. It looks like I could create a 
generic query by introspecting the has_many relationships.
The only thing that still bugs me is the fact that userland code will 
have to use the table alias prefixes in the columns, but maybe I've won 
enough for one day.

Thanks for your input, and for making me think this through even 
further. And more generally, thank you for an excellent framework. It 
has saved me a tremendous amount of time, and made interfacing with the 
database fun again :-D

Rhesa