misterb101 has asked for the wisdom of the Perl Monks concerning the following question:

Hi fellow Perl hackers, I am working on a large application in which I use DBIX::Class as an ORM. I have a 'Document' resultset that is linked up to a large number of other resultsets using many_to_many relations. This allows me to define what documents are related to other database objects. Now I want to get a list of Documents, easy:
my $documents = $schema->resultset('Document')->search();
For every document that is returned I want to display to which other objects it is actually connected using a many_to_many relation without manually having to go through all the many_to_many relations the object has. This allows me to more easily expand the number of objects the 'Document' object is connected to without having to redefine the code above. Can anyone tell me if it is possible to list which many_to_many relations a resultset has and which relations actually have a connected object?
while (my $document = $documents->next) { my $related_objects = $document->HOW_DO_I_DO_THIS(); ... }
If anyone can help me I would be VERY gratefull

Replies are listed 'Best First'.
Re: list DBIX::Class many_to_many relations
by chrestomanci (Priest) on Dec 18, 2010 at 21:25 UTC

    Any DBIx::Class ResultSet encapsulates an SQL query. What you have to do it work out how to get back what you are looking for as a query result from your relational database, and then construct a suitable query that will give you that. See DBIx::Class::ResultSet

    For your specific problem, you say that you have a many_to_many relationship between your 'Document' class and something else. You don't say what, so I am going to use 'Author' as an example. (Lets pretend each document has many authors.) (See: DBIx::Class::Relationship)

    For such a many_to_many relationship to work, you would need to have a join table, so your Classes would read:

    package Schema::Document; use base 'DBIx::Class::Core'; __PACKAGE__->table('tblDocument'); __PACKAGE__->add_columns( 'id' => { data_type=>'int', is_auto_increment=>1 }, # Other cols. ); __PACKAGE__->set_primary_key('id'); __PACKAGE__->has_many('document_authors', 'Schema::Document_Author', ' +author'); __PACKAGE__->many_to_many( 'authors' => 'document_authors', 'author' ) +; package Schema::Author; use base 'DBIx::Class::Core'; __PACKAGE__->table('tblAuthor'); __PACKAGE__->add_columns( 'id' => { data_type=>'int', is_auto_increment=>1 }, # Other cols. ); __PACKAGE__->set_primary_key('id'); __PACKAGE__->has_many('document_authors', 'Schema::Document_Author', ' +document'); __PACKAGE__->many_to_many( 'documents' => 'document_authors', 'documen +t' ); package Schema::Document_Author; use base 'DBIx::Class::Core'; __PACKAGE__->table('tblDocument_Author'); __PACKAGE__->add_columns( 'id' => { data_type=>'int', is_auto_increment=>1 }, 'author_id' => { data_type=>'int' }. 'document_id' => { data_type=>'int' }. ); __PACKAGE__->set_primary_key('id'); __PACKAGE__->belongs_to('document' => 'Schema::Document_Author'); __PACKAGE__->belongs_to('author' => 'Schema::Document_Author');

    The point of this illustration, is to remind you that you have a join table between your Documents table, and the other table that it has a many to many relationship on. Could you do queries on that table?

    Secondly, can I draw your attention to the fact that you can chain resultSets together, to give you back a resultSet that encapsulates a very complex query.

    Start with your documents table. Presumably you have a query that returns a subset, Eg, those from a particular year.

    my $docsByYear_resultset = $schema->resultset('Document')->search({ year => 2001 });

    If you do $docsByYear_resultset->as_query() You will get back the SQL query on your Documents table. It should be something fairly simple, with WHERE year = 2001 or suchlike

    Then lets suppose you want a resultSet that returns all Authors of any documents from that year. So we create a resultSet from the first, that only returns the document IDs, and use that to query the join table:

    my $doc_ids = $docsByYear_resultset->search({}, { select => [ 'id' ]});

    If you run $doc_ids->as_query() You will see the same query, but this time only selecting the id Col on the Documents table, and nothing else.

    Now the clever part, we can feed that query into another one on the Author table, pulling in the Document_Author table:

    my $authorsInYear_RS = $schema->resultset('Author')->search({ 'document.document_id' => { 'IN' => $doc_ids->as_query() }, }, { prefetch => { document_authors => 'document' } });

    To explain what is going on from the bottom. The prefetch clause pulls in the 'document_authors' one_to_many relationship to the 'Document_Author' join table, and names the pulled in join as 'document'. The query part the asks for all Authors, that have an entry in that join table, where the 'document_id' column matches the list of id's from the first query. If you do $authorsInYear_RS->as_query() Then you will see the very complex bit of SQL that DBIx::Class has constructed for you. The beauty of this approach, is that $authorsInYear_RS is a live query, so if the contents of your database changes, then so will the list of results returned by that resultSet. So for example you can do $authorsInYear_RS->count() any time you like, and will always return an up to date number.

    Does that answer your question?

      Hi there, Thanks a lot for you answer! Although it did not exactly answer my question it taught me a LOT about DBIC that I did not know yet. Thanks for that! What I was actually looking for was fetching all many_to_many related objects without exactly knowing the names of the relations. I have solved it by doing:
      my @related_models; # fetch relations from model. By doing it this way when new many_t +o_many relations are created, the controller automatically picks them + up my @relations = $self->schema->source('Document')->relationships() +; foreach (@relations) { if ($_ =~ m/(^.*)_documents$/) { push @related_models, $1; } } foreach (@related_models) { my ($relObj) = $object->$_(); #Assume only one object refe +rs to this one (business logic) if ($relObj) { $row{relatedObject} = $relObj->name(); $row{relatedObjectId} = $relObj->id(); $row{relatedObjectType} = $relObj->_source_handle->sou +rce_moniker; # Why is getting the source Name so difficult? } } push @data, \%row; } Again thanks for the very informative reply!