Re: [CDBI] Sharing Queries with set_sql

> Typically when using Class::DBI and needing a complex query we've

On 6 Sep 2005, at 15:33, Tom Adamo wrote:

> Typically when using Class::DBI and needing a complex query we've  
> been using the set_sql method:
>
>   package App::DB::TableA;
>   use base qw( App::DB );
>
>   __PACKAGE__->table( 'tableA' );
>   __PACkAGE__->columns( Primary => 'id' );
>   __PACKAGE__->columns( TEMP => qw( field_b field_c );
>
>   __PACKAGE__->set_sql( some_query => qq{
>        select a.id, b.field_b, c.field_c
>          from tableA a, tableB b, tableC c
>         where a.id = b.id
>           and b.id = c.id
>   };
>
> When we want to add additional queries we will add it to this file,  
> and add more TEMP columns were necessary.
>
> What we've run into is wanting to be able to share the queries  
> amongst all our developers but haven't figured out a way to do this  
> without defining a table and temp columns.  We would like to  
> accomplish the following:
>
>   package Shared::DB::SQL;
>   use base qw( Class::DBI::Oracle );
>
>   __PACKAGE__->set_sql( some_query => qq{
>        select a.id, b.field_b, c.field_c
>          from tableA a, tableB b, tableC c
>         where a.id = b.id
>           and b.id = c.id
>   };

I'm not quite sure which part of this is the question, so please  
excuse the tangents.

If it's about generalising and sharing queries, then http:// 
wiki.class-dbi.com/index.cgi?UsingJoins should help.

If it's about returning values from more than one table and  
generating reports from within cdbi classes, the usual answer is  
'don't: it's not the right tool'. Do it at the template level, and if  
that's too inefficient then write a custom reporting class: cdbi  
becomes slippery and awkward if you don't stick to the simple  
correspondences it expects.

(The TEMP hack you have is neat but feels deeply wrong: you'll end up  
with objects of the same class that have different columns depending  
on the query that produced them, some of which update back to the  
database and some of which don't. You'll end up peppering the code  
with tests to see what your objects are like, which seems to me the  
sort of thing one is trying to get away from with cdbi.)

If your question is about smoothing over the interface so that people  
don't have to do horrible things like this

     return $class->sth_to_objects( $class->sql_From_All_Keywords 
( $keywords, $count, 'date DESC' ));

which to my shame I have just plucked from working code, then I'm all  
for it and your method looks good (apart from the TEMP thing :). I'd  
be tempted to put it in AUTOLOAD and test against $class->sql_names:  
that would allow you to call $class->some_query(values), though it  
might cramp your namespace a bit.

i hope there's something useful in here.

best

will

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi