in reply to Search for ORM with Multi-Table-Object Support

Why did you reject DBIC? It's probably what you want. The following is a brief tour of its power.

Multi-Table-Mapping-Objects

Pseudo-code which assumes some manual set-up in the schema files (many to many relationships are not discoverable and the example would require overload on the role class to work as shown)– 

for my $user ( $users->all )
{
    print $user->name, $/;
    print "    Roles: ", join(", ", $user->roles) || "none", $/;
    for my $role ( $user->roles )
    {
        print "Users in this role: ";
        print join(", ", map { $_->name } $role->role->users);
    }
}

[download]

DBIC has notions of the column, the resultset, the table, the record, the connection, the relationships, and more. Many ORMs are record oriented and get ugly/stuck easily since it's all a ball of mud around a row. DBIC handles this really nicely.

Lazy-Loading (Single Columns as well as groups of columns)

my $full_rs = $schema->resultset("user");
my @ids = $full_rs->get_column("id")->all;

# -or-

my $simple_rs = $full_rs->search({},
                                 { columns => [qw/ id /] });

print $_->id, $/ for $simple_rs->all;

# -or, no objects-

# use parent "DBIx::Class::ResultSet::HashRef"; <- in resultset class.
my $ids = map { $_->{id} }
      $schema->resultset("user")
             ->search({},{columns => ["id"])
             ->hashref_rs
             ->all;

[download]

Agregation Functions

The get_column above is an example. DBIx::Class::ResultSetColumn has more, like– 

printf "\$%.2f\n", $user->orders->get_column("total")->sum;

[download]

All sorts of relationships

Also see the amazing DBIx::Class::Tree::Mobius for an inner-relationship hybrid of nested intervals and materialized paths.

Flexible ways to query data from database

Chained resultsets are everything good in the world. You essentially build up complementary named fragments and make your code compact, syntactical, and fun. For example (assumes you wrote the resultset code, which is easy, to do it)–

  $schema->resultset("Company")
         ->find($id)
         ->users
         ->active
         ->with_overdue_balance;

[download]

Multi-Database-Support

No. Yes. What?

DBIC supports pretty much everything DBI does. It does not support joining across DBs. You can certainly have a schema instance for each DB exchanging information at the Perl level.

More reading

Update: missing adverbly.

Replies are listed 'Best First'.
Re^2: Search for ORM with Multi-Table-Object Support
by Xel (Novice) on May 02, 2012 at 21:11 UTC

    Thanks for your answer,

    I am not sure if you mean the same as I do, when refering to Multi-Table-Mapping-Objects.

    I mean an object, which consists of one row out of one table and another one out of another table.

    What I would wish to do is

    # Tablesetup:
[user]
id
username
password

[user_contact]
id
firstname
lastname
street
zip
city
phone

[user_profile]
id
profile_text
profile_image

# Code-Example

$user->load({id => 1, fetch => qw('username firstname lastname')});

print $user->firstname; # prints prefetched firstname
print $user->lastname; # prints prefetched lastname
print $user->profile_text; # prints lazy-loaded profile_text

    [download]

    As I understand DBIC I would do the following (ignoring lazy-loading): 

    $user = $schema->resultset('User')->find(1);
print $user->user_contact->firstname;
print $user->user_contact->lastname;
print $user->user_profile->profile_text;

    [download]

    Well, of course I could get around this limitation by writing my own setters and getters accessing the sub-objects from within the parent-object. Maybe this is the best way to do it, but I tought there may be ORMs which can handle this behavior already.

    Regards
    Alex

[reply]
[d/l]
[select]

      It might just be personal bias but that feels messy compared to the affordance it gives. You could potentially do the same thing with Moose using handles on the foreign objects attributes. That seems like a mistaken can of worms and a performance ding though… As you mentioned, you could write your own shortcut routines and you could make it semi-lazy with has_column_loaded.

      There are two natural ways you can do some of what you want in DBIC. The first is keeping the DB in line with the code: a VIEW. This is mysql syntax– 

      CREATE VIEW uview AS
  SELECT
       u.id
       ,u.username
       ,c.name
       ,c.address
       ,p.text
       ,p.image
  FROM user u
       ,contact c
       ,profile p
  WHERE c.user = u.id
        AND
        p.user = u.id
        ;

      [download]

      And then you create a regular DBIC result class for it. I tried a loader with dbicdump and it seems to work just fine.

      Alternatively you could do a DBIC in-code view class. With __PACKAGE__->result_source_instance->is_virtual(1) and the same SQL as the result_source_instance->view_definition. Both of these suffer from returning readonly objects and the real VIEW additionally will likely create real, and ultimately useless/confusing, tables upon deployment while the in-code view will not. (Update: it occurs to me that could put the is_virtual on the real VIEW too to keep it from creating a new table on $schema->deploy… but I didn't test and it's not put in by the loader/dumper defaults.)

      But for convenience and a sort of permanent join/prefetch this would do what you want (as long as you only want to read).

[reply]
[d/l]
[select]
      DBIC is simpler than SQLAlchemy, so it doesn't do this kind of thing for you. Personally, I like the obvious separation of the objects: It makes the data model and relationships more apparent from the code (though I would remove the user_ prefix from the relationships). I've rarely had all of my code (DBIC or SQLAlchemy) survive database schema changes, unless I had planned for that schema to change (simple adding/removing optional columns for example).
[reply]
        Personally, I like the obvious separation of the objects: It makes the data model and relationships more apparent from the code

        Sure - I'd have said something alike two years ago, too.

        But today I have to deal with a complex database structure, which is build out of at least 400 tables per Project-DB (with somewhat around 30 project DBs most of which have "nearly" identical structures) which has historically grown and is messy like hell. And it is my job to change that, make the database perform better, be more flexible and stuff - with nearly no man power to change the querys in the constantly growing and changing application.

        Starting with an ORM opens the door for constantly changing access from pure sql to an object oriented way and when this is done I wish to be able to change objects and database together without the need to let the app or the developers change the way they work. I belive this only to be possible if objects are not to closely linked to the data model and relations.

        So please understand that I have no room for "It's nicer if the objects line up nicely with the data-model"-Stuff. Yes it surely is, you are perfectly right, but I don't need it nice, I just need it working and changeable without to great effort.

[reply]

      DBIx::Class certainly has what you are asking for with has_one, and combining Subset_of_related_fields and Incomplete_related_objects.

      I'd put together an example for you but its all right there.

      Also, you didn't mention you'd be doing updates, as mentioned elsewhere I believe a view would make the most sense.

[reply]

In Section Seekers of Perl Wisdom