Re: Removing auto-generated methods (set_sql)

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

On Mon, Jun 14, 2004 at 10:59:13PM +0100, Tony Bowden wrote:
> On Mon, Jun 14, 2004 at 12:57:38PM -0400, William McKee wrote:
> > My script is being run in a mod_perl environment. I need to dynamically
> > generate a search based on input to a web form. This search requires a
> > join so I can't use standard CDBI methods. The WHERE clause could change
> > from call to call thus I regenerate it using SQL::Abstract.
> 
> If the SQL is going to change each time, then there's no point in
> creating a method for it. The method should be generic enough to cope
> with whatever input it gets.
> 
> Consider the internal search() method.
> 
> Its SQL is just:
> 
> 	SELECT __ESSENTIAL__
> 	FROM   __TABLE__
> 	WHERE  %s
> 
> Then given @cols, @vals pairs, it interpolates that %s into,
> basically: join "$_ = ?", @cols, and executes it with @vals.
> 
> If you're doing a join across two tables, you can do something similar
> with:
> 
>   SELECT c1.id    (or whatever your table1 essential column(s) is/are)

If  SELECT __ESSENTIAL(Class::One)__

doesn't work, then shouldn't it be made to?

>   FROM   __TABLE(Class::One=c1)__,
>          __TABLE(Class::Two=c2)__
>   WHERE  __JOIN(c1 c2)__
>   AND    %s
> 
> And then expand that %s similarly (adding the c1 / c2 prefix to qualify
> your column names). [I should really factor out the code that converts a
> hash to a SQL with placeholders...]

Yeap :)

Tim.