To answer some of your questions in kind of random order:

When declaring a relationship, if you know you want it to use an inner join, you can specify that in the relationship declaration:

__PACKAGE__->has_many( 'foos', 'MySchema::Foo', 'bar_id', {
    join_type => 'INNER'
} );
[download]

To find out exactly what queries are being issued as a result of your code, set the environment variable DBIC_TRACE to a true value, and the generated queries will get dumped to stderr.

As for whether it's efficient or not, that depends a lot on the application, the data, and the types of queries you are doing, as well as how you define efficient. I'm sure that I could get queries that ran a little bit faster by building them by hand, but the time that it takes to do so wouldn't offset the time that DBIx::Class saves me, so while the queries themselves might be slightly less efficient, I am much more efficient with it than without.

I am not fond of DBIx::Class, as it can be fairly complicated. There are so many places customization and configuration can reside it can take a while before you find what you are looking for. You should really think of it as its own application rather than easy to use modules.

I can understand why they made some of the decisions that did. It was supposed to be generic enough to support most databases and what every database designs someone came up. To address these problems required some patch-work coding which made it messier than it needed to be.

Basically, if you have a relatively good design you can get through DBIx::Class with only a few headaches. If you have a crappy design, your going to have a lot of headaches. I still suggest that you learn the in and outs of DBIx::Class because it will be easier than creating your own solution.

Also, depending on your database, an alternate object-relational mapper to try is Rose::DB. It also has a learning curve, but a number of prominent people think that the design is better. Also the author is surprisingly responsive to questions on the list.

I think you mean Rose::DB::Object. (Rose::DB is a DBI wrapper and data source registry. Rose::DB::Object is an ORM.)

Heh. I didn't mean to give you an opportunity to demonstrate your responsiveness. :-)

Second, search() does INNER JOINS by default for has_one and belongs_to and LEFT JOINS by default for has_many.

The other piece you might be missing is everything is based on relationships. That's what join and prefetch are all about.

The final magic bit is the idea of the resultset. You can think of a resultset as a family of SQL statements (but it's a lot more than that). You can build resultsets from other resultsets and execute different SQL statements from the same resultset object. The X_related methods call X on the resultset found by the _related() relationship, but bound by the conditions set in the initial resultset. Oh - and you can build resultsets till the cows come home, but you don't actually touch the database until you get a row object.

Thanks for the suggestion, but I'm going to “stick to what I've chosen” for this one, and perhaps consider different things for the next one. (As we all do.)

But, having said that, I do think that what I'm seeing looks pretty good... that it ought to do the job well once I understand it.

I did use the Schema::Loader to build the basic schema (based on an existing database), and I think it did a very good job at its assigned task. So, I'm not worried that I've got any “disconnects” on that score. But I've surely got a wild race-horse on my hands here, and it's very difficult to ride.