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.
| [reply] |
- 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. - It is still just Perl: wrap the statements you wish to check in an eval.
- 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
| [reply]
[d/l] |
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.
| [reply]
[d/l]
[select] |
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 > ?');
There's also "make_manager_method_from_sql" which can install a query like this as a method in your object manager class. | [reply]
[d/l] |