On 10/19/05 10:10 PM, Terrence Brannon wrote:
> Well, anyone considering a migration from Class::DBI would want the type of
> functionality it offers for custom SQL. That's why I watch this list and
> actively try out your module. I like what I've seen so far, but was just
> asking about custom sql because I have seen nothing regarding this feature
> that I use myself quite often.
If you mean set_sql(), then objects_from_sql() will basically do the same
thing, sans the method creation. I could add a method_from_sql() wrapper to
complete the picture.
But in practice, I'm much more likely to write my own methods that call
through to get_objects() because that way I get all the nice coercion and
formatting behavior that I expect. For example, here's the Class::DBI way:
Product->set_sql(new_products => qq{
SELECT __ESSENTIAL__
FROM __TABLE__
WHERE released > ?
});
@recent = Product->new_products('2005-01-01 05:00:00');
The "longhand" RDBO way looks like this:
package Product::Manager;
...
sub new_products
{
shift->get_products(query => [ released => { gt => shift } ])
}
The advantage of the RDBO way is that I can make calls like this:
@recent = Product::Manager->new_products('1/1/2005 5pm');
@recent = Product::Manager->new_products($dt); # DateTime object
without worrying about the exact type of the "released" column or the
details of how the current database wants those values formatted.
The only time something like objects_from_sql() is necessary is if you want
to do some fancy stuff in the SELECT portion of the query. Something like:
sub products_with_bfc
{
shift->products_from_sql(sql => <<"EOF", @_);
SELECT
p.*,
(SELECT COUNT(*) FROM foo WHERE p.id = foo.product_id and foo.bar > 10)
AS big_foo_count
FROM
products
EOF
}
where the Product class would have a big_foo_count() attribute that normally
runs the "COUNT(*) ..." subquery above when it's called. The
products_with_bfc() manager method would pre-fill that attribute so that the
query doesn't have to be run on demand.
But honestly, I'd much rather solve this another way:
use constant BFC_SQL =>
'SELECT COUNT(*) FROM ' . Foo->meta->table . ' f '
'WHERE t1.id = f.product_id and f.bar > 10';
Product->meta->columns
(
big_foo_count => { sql => BFC_SQL },
...
);
Too bad that doesn't actually work (yet :) In the meantime, I'll probably
add the objects_from_sql() method as a stopgap.
So, am I missing anything about custom SQL? Can you give me an example of
something you commonly do that can't be done as easily in RDBO?
-John
-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Rose-db-object mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/rose-db-object
