1. From the docs of DBIx::Class::ResultSet:

   search_literal

   Arguments: $sql_fragment, @bind_values

   Return Value: $resultset (scalar context), @row_objs (list context)

   my @cds   = $cd_rs->search_literal('year = ? AND title = ?', qw/2001 R
   +eload/);
   my $newrs = $artist_rs->search_literal('name = ?', 'Metallica');
   [download]

   Pass a literal chunk of SQL to be added to the conditional part of the resultset query.

2. DBIx::Class::Exception perhaps?
3. You can make a resultset, stuff it into a variable and keep it around without executing it and when you need it you can add to this results extra conditions and such and execute it. Unless and until you ask for the data in the resultset it will not make a trip to the database so its costs is minimal.

Thanks, I'm not 100% sure this gives me what I need, so I'll elaborate in case you or other posters can fill in some details.

1. You suggestion allows an arbitrary Where clause to be added. I want a completely aribitrary SQL statement.

2. I read DBIx::Class::Exception, but one has to explicitly throw an exception. I'm looking for code that will pass errors back to the caller if something fails. At least, that's how I'd write it.

3. You mention stuffing a resultset and throwing it into a variable. I want a resultset for commonly used joins. If I'm always joining tables "Order" and "Items", for example, it makes sense to have a pre-defined ResultSet where I can just invoke My::Schema:OrderItems to get the joined results with appropriate where parameters sent as arguments. Can this be done? I haven't seen docs on it, but I assume it can.

1. The where clause is what causes most problems as sometimes it gets very complicated to construct it as a combination of hashes and arrays and then an arbitrary where clause is more easy to use. The SELECT, FROM, GROUP BY, ... are much easier to build, so there is no real need for an "arbitrary" version.

   Having a complete arbitrary SQL clause would defeat the whole idea of DBIx::Class : you will loose the whole OO-part of it. DBIx::Class needs to know how your SQL is constructed to perform its OO-magic (triggers, inflating and deflating fields, joins, accessors, ...). That is why the "arbitrary SQL" from DBIX::Class has to go through the trick of using it as a sub-select in a custom ResultSource.

   If you really need to use raw arbitrary SQL, DBIx::Class::Storage::DBI->dbh provides access to the DBI database handle, but I never yet found any reason to use this.

2. It is still just Perl: wrap the statements you wish to check in an eval.
3. Perhaps you can make a view of this join and use that?

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

I'm sure you can do arbitrary SQL with DBIx::Class. I use Rose::DB::Object. Here's an example from the docs of how it handles arbitrary SQL:

    package Product::Manager;    
    use Product;
    use base 'Rose::DB::Object::Manager';
    sub object_class { 'Product' }
    ...

    $products = Product::Manager->get_objects_from_sql(<<"EOF");
    SELECT * FROM products WHERE sku % 2 != 0 ORDER BY status, type
    EOF

    $products = 
      Product::Manager->get_objects_from_sql(
        args => [ '2005-01-01' ],
        sql  => 'SELECT * FROM products WHERE release_date > ?');
[download]

There's also "make_manager_method_from_sql" which can install a query like this as a method in your object manager class.

I'm afraid I can't provide any useful comment on your DBIx::Class specific questions. However if arbitrary SQL is as important to you as the ORM part I would like to suggest perhaps a cursory look at SQL::DB. (Disclaimer: I am the author of that module).

The following is a real example from one of my own projects:

    my @nodes = $self->fetch(
        select    => [
            $nodes->id,
            $nodes->path,
            coalesce(
                $nodest->nav,
                $nodest->title,
                $nodes->nav,
                $nodes->title)->as('nav'),
        ],
        from      => [$nodes,$nodes2],
        left_join => $nodest,
        on        => ($nodest->transof == $nodes->id) &
                     ($nodest->lang == $self->lang),
        where     => $nodes2->lft->between($nodes->lft, $nodes->rht) &
                     ($nodes2->id == $id),
        order_by  => $nodes->lft,
    );
[download]

The objects returned from this query are blessed into a dynamically created class that contains only 'id', 'path' and 'nav' methods. The advantage of this style of query is that you can write anything you like, and retrieve exactly (and only) the information you want from the database. The disadvantage (compared with ORM-style DBIx::Class) is that you have to construct for each query with exactly what you want. Ie: no automatic joining of tables, and no auto-inflation.

Unfortunately I have to follow with the caveat that SQL::DB is still under development and suffers from lack of documentation and exposure outside my own projects. However (and here the true motivation for this comment) I would certainly appreciate feedback and knowing how SQL::DB is/isn't suitable for your task.

#!/usr/bin/perl
use strict;
use warnings;

package MySchema::ArbitrarySQL;
use base qw/DBIx::Class::Core/;

__PACKAGE__->table('dummy');
__PACKAGE__->add_columns(qw/num/);
__PACKAGE__->result_source_instance->name(\'(select 10*10 as num)');

package MySchema::LaterSQL;
use base qw/DBIx::Class::Core/;

__PACKAGE__->table('dummy');
__PACKAGE__->add_columns(qw/num/);

package MySchema;
use base qw/DBIx::Class::Schema/;

__PACKAGE__->register_class(ArbitrarySQL => 'MySchema::ArbitrarySQL');
__PACKAGE__->register_class(LaterSQL => 'MySchema::LaterSQL');


package main;

my $schema = MySchema->connect(
 "$yourdsn",  "$youruser", "$yourpass", {'RaiseError' => 1}
);
$schema->storage->debug(1);

my ($r) = $schema->resultset('ArbitrarySQL')->all;
print $r->num, "\n";

#your can even change the sql later
print $schema->source( 'LaterSQL' )->name("(select 100*100 as num)");
print "\n";

($r) = $schema->resultset('LaterSQL')->all;
print $r->num, "\n";

#but after one call, you can not change the sql
print $schema->source('LaterSQL')->name("(select 1000*1000 as num)");
print "\n";

($r) = $schema->resultset('LaterSQL')->all;
print $r->num, "\n";  #you still got 10000, that's bad.
[download]