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 06:27:48PM +0200, Rhesa Rozendaal wrote:
> 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');

That's far too much of a shortcut!

> The example from the manual says this:
> Music::CD->set_sql(new_music => qq{
>                 SELECT __ESSENTIAL__
>                   FROM __TABLE__
>                  WHERE year > ?
> Clearly it passes the arguments as bind values to the statement, so it 
> does _not_ use the %s.

There is no %s in that query :)

The equivalent with %s would be:
	SELECT __ESSENTIAL__
	FROM __TABLE__
	WHERE %s > ?

Then you would call it with
  my $sql = $class->sql_new_music('year');
	$sql->execute($year);
	return $class->sth_to_objects($sth);

The initial call to get the SQL should pass in the parameters that will
be interpolated into the %s variables. The execute() will then pass the
normal DBI placeholders. It's a two pass system for maximum flexibility.

The short-cuts that are in place will fall down when you try to do
anything more complicated than the simple tasks they were made for.

There are plans to factor out a lot more of the search code in the next
release to make it easier for users to write their own more complex searches
without overriding huge swathes of code.

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

Yeah, but you're not usting the Class::DBI SQL JOIN expansion there!

> 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

Thanks!

Tony