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?
| 