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?

I appreciate the time you've taken to respond, CountZero. I have some comments (as usual :-) )

1. While what you say is true in most cases, I can think of several examples where this doesn't apply. UNIONs, ALTER TABLE (while rare), and invocation of stored procedures in some DBs would fall into this category. Also, vendor-specific commands like SHOW TABLE STATUS in MySQL would require arbitrary SQL statements. If DBIx::Class can do this using the standard syntax, I'd be thrilled to learn how.

2. eval would work and it's a good suggestion, but it's inelegant and really, error handling should be built in. DBIx::Class seems well-written, at least from the outside, so I was hoping this was something on which I could rely.

3. Some DBs don't support views and this seems like a very hackish workaround for what should be a simple and common task. I did, however, discover that I can do most of what I want from DBIx::Class::Manual::Joining. I'm just not sure how to make a ResultSet that does this by default.

Perhaps it is a good idea to have a look at the DBIx::Class mailinglist. The real DBIx::Class-gurus hang out there.

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

eval would work and it's a good suggestion, but it's inelegant and really, error handling should be built in.

A function which returns any number of objects, some being exceptions, other being results is what I see as inelegance. Having to explicitly test every return value not just for success--which is an easy && good Perl idiom--but also for type is nasty. Error handling at the application level is nice, so it's done only once. Otherwise it is required at every level in every conceptual unit; a nice petri dish for spaghetti.

    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]