Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

The (Too-)Well Hidden Magic of "DBIx::Class"

by sundialsvc4 (Monsignor)
on Feb 04, 2009 at 15:07 UTC ( #741307=perlquestion: print w/ replies, xml ) Need Help??
sundialsvc4 has asked for the wisdom of the Perl Monks concerning the following question:

I guess I just don't get the correct way to use “DBIx::Class.” The documentation is simply sketchy... I need some help to wrap my mind around this (no-doubt, wonderful...) thing.

What I have to deal with is fairly basic:   retrieving related records, all 1:1, from a moderately-sized “fan” of related tables. A couple of these need to be inner-joins since the related records may or may not exist. I've auto-built a schema and it appears to have correctly captured the database relationships. So far, so good. I guess that I'm seeing “two ways to do it,” and I don't know which one is preferable.

One strategy, which I managed to perfect yesterday (and the behavior I was seeing may have been a bug in the package), is to use join= and prefetch= clauses in a search() call. I'm not quite sure if it is issuing INNER JOINs or not ... and I haven't quite figured out yet how to tell. But it does retrieve data from all the related tables (when records exist in all of them).

The second approach is find_related(), and I have yet to find a good, complex, example of this. It's one thing to simply build a long “train” of “related”s, but how do I extend things (using that approach) to build branches? How do I extend the graph at any point other than its (one...!) “leaf?”

And finally... what price am I paying? Is it efficient or not? How can I easily watch what it is generating?

All these questions. Such basic (I know...) questions...

Comment on The (Too-)Well Hidden Magic of "DBIx::Class"
Re: The (Too-)Well Hidden Magic of "DBIx::Class"
by jasonk (Parson) on Feb 04, 2009 at 15:29 UTC

    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' } );

    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.


    www.jasonkohles.com
    We're not surrounded, we're in a target-rich environment!
Re: The (Too-)Well Hidden Magic of "DBIx::Class"
by Herkum (Parson) on Feb 04, 2009 at 15:42 UTC

    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.

Re: The (Too-)Well Hidden Magic of "DBIx::Class"
by tilly (Archbishop) on Feb 04, 2009 at 20:05 UTC
    Random point of confusion that needs to be clarified. You seem to think you want inner joins. But if the related record may not exist, what you really want is an outer join.

    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.

        Heh. I didn't mean to give you an opportunity to demonstrate your responsiveness. :-)
Re: The (Too-)Well Hidden Magic of "DBIx::Class"
by sundialsvc4 (Monsignor) on Feb 04, 2009 at 22:30 UTC

    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.

Re: The (Too-)Well Hidden Magic of "DBIx::Class"
by dragonchild (Archbishop) on Feb 05, 2009 at 01:53 UTC
    The big key here is to only use the pieces you actually need to use. Then, when you find yourself bumping against something, then ask how to do that thing, explaining what your'e trying to do. I've been using it on and off for 2 years, doing some really awesome things, and I do some development on it, but I still learn new things every week or so just by hanging out in #dbix-class.

    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.


    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://741307]
Approved by xdg
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2014-07-29 02:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (211 votes), past polls