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

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?


> 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)
>   FROM   __TABLE(Class::One=c1)__,
>          __TABLE(Class::Two=c2)__
>   WHERE  __JOIN(c1 c2)__
>   AND    %s

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

    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.

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


> Because it's a method, and you're trying to change it for every instance
> of the class, current or future. That's not really what methods are for. 

That's just me trying to fit a square peg in the round hole <g>. Thanks
for showing me a better way.


William

        -- 
        Knowmad Services Inc.
http://www.knowmad.com