Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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?

In reply to Re: list DBIX::Class many_to_many relations by chrestomanci
in thread list DBIX::Class many_to_many relations by misterb101

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (2)
As of 2023-06-04 13:44 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (21 votes). Check out past polls.