Re: Removing auto-generated methods (set_sql)

[prev] [thread] [next] [Date index for 2004/06/15]

On Tue, Jun 15, 2004 at 01:53:30PM -0400, William McKee wrote:
> OK, this makes sense. I didn't see anything in the CDBI docs about
> running a single SQL statement except the retrieve_from_sql which cannot
> support joins. Perrin's reply to drop down to retrieving the dbh and
> calling DBI directly seems the best approach. Perhaps that should be
> documented and/or added to the wiki?

Feel free to add it to the wiki. If people come up with a good wording
I'll certainly look at adding it to the main docs.

> So could I do something like the following:
>     $class->set_sql(my_query => <<"");
>     SELECT *
>     FROM   __TABLE(Class::One=c1)__,
>            __TABLE(Class::Two=c2)__
>     WHERE  __JOIN(c1.id c2.user_id)__
>     AND    %s

Not quite. You literally just do the __JOIN(c1 c2)__. It'll know what
columns to join on based on the relationships you've already set up
between them. And you probably don't want a SELECT * - you want it to be
the ESSENTIAL columns of $class: c1.id is often sufficient.

>     my @recs = $class->my_query({c1.field1 => $val1, c2.field3 => $val3});
> Is there a way to control how the fields and values that are bound to %s
> get compared? I'm guessing that the default is '='. I need to be able to
> do LIKE and ILIKE comparisons at the very least.

It's up to you to define this in the method that calls sql_my_query.

So, if you want a LIKE query, you'll need to do something like
(untested):

	sub my_join_search { 
		my ($class, $args) = @_;
		my $where_clause = join " AND ", map "$_ LIKE ?", keys %$args;
		return $class->sth_to_objects(
			$class->sql_my_query($where_clause), values %args
		);
	}

To be really robust you'll need to cope with overriden accessor names
etc, and you can abstract away that LIKE to be more generic so you don't
have to repeat yourself for ILIKE or EQUALS or whatever. But that should
give the gist. It's how search() works in Class::DBI itself, and you can
always look at it for some more details on how to abstract this further.

And, as I said earlier, I should really abstract more of this out
further so that writing these yourself is even easier...

> BTW, where is the __JOIN__ substitution defined? I do not see any
> references to it in the docs.

It's defined in transform_sql(). 

It's not actually documented, yet :)

Tony