[Catalyst] DBIC and RDBO compared (was: Choosing the right ORM)

[Brandon Black]

On 11/30/05, John Siracusa <siracusa at mindspring.com> wrote:
> On 11/30/05 7:23 PM, Perrin Harkins wrote:
> > One important feature which Class::DBI supports (and I assume DBIC
> > supports this as well) is the use of arbitrary SQL to fetch objects.
> > Some database operations are just much better expressed in SQL than in a
> > complex perl data structure, especially if you want access to vendor-
> > specific extensions like the Oracle tree stuff.  With Class::DBI, you
> > can write some hairy SQL query and let it do all the drudge work to turn
> > the resulting rows into objects.  This is a key feature for me.
>
> RDBO supports raw SQL in the WHERE clause, if desired.  There's also a
> get_objects_from_sql() Manager method that's currently commented out:
>
> http://search.cpan.org/src/JSIRACUSA/Rose-DB-Object-0.54/lib/Rose/DB/Object/
> Manager.pm
>
> I just can't think of anything it can do that can't be done better using one
> of the other features.  If you can give me some example scenarios, that'll
> help me get an idea of what people actually use this kind of feature for.
>
> -John
>

I've brought this up before either here or on the DBIx::Class list,
but one interesting problem I've seen that might give you some food
for thought is SELECTing things other than raw column data, using the
RDBMS builtin functions or user-supplied stored procedures, either of
which may or may not be an aggregating function.  A lot of my queries
leverage the RDBMS for data aggregation or post-processing.  For
example, in raw SQL:

SELECT min(col_x) as min_col_x, stddev(col_y) as stddev_col_y FROM
atable WHERE itemid IN ( 123, 456, 789, 1234 ) AND t_stamp =
1142412412;

on a table where itemid and t_stamp comprise a compound primary key. 
Of course in the real world there's more like 10+ functions being
selected in a query, 30+ "itemid"s, and a whole range of t_stamps,
aggregating hundreds of rows down to a handful of result values.

In DBIx::Class, I formulate it something like: (this is from memory, I
don't have the code in front of me at the moment, might contain
errors...)

my @res = $atable_class->search(
    { itemd => [ 123, 456, 789, 1234 ], t_stamp => 1142412412 },
    { cols => [ 'min(col_x)', 'stddev(col_y)' ] }
);
my $min_col_x = $res[0]->get_column('min(col_x)');
my $stddev_col_y = $res[0]->get_column('stddev(col_y)');

Which I consider to be fairly reasonable all things considered (I was
shocked that it even worked the first time I tried this method). 
However it is a bit more klunky than it neccesarily has to be, and I
think there must be a better way to abstract the idea of selecting
functions and/or aggregate functions off of database columns better
than this.  This case is relatively simple, other complexities lurk in
handling the whole general case correctly (especially if you get into
GROUP BY clauses and all that).

-- Brandon