Class::DBI::DeepAbstractSearch

From ClassDBI

Class::DBI::DeepAbstractSearch allows you to do complex searches using joins. While initially confusing to use, I can assure you it is an important missing link in the Class::DBI world.

The CPAN docs are here:

http://search.cpan.org/~sriha/Class-DBI-Plugin-DeepAbstractSearch/DeepAbstractSearch.pm

Here is an example, as the CPAN one is somewhat confusing:

 {
  package Library::Book_xref;
  use Global;
  use base 'Library::Connection::DB'; # setup your db connection
  use Class::DBI::Plugin::DeepAbstractSearch;
  __PACKAGE__->set_up_table( "books_xref");
  __PACKAGE__->has_a( book_id => 'Library::Book' );
  __PACKAGE__->has_a( author_id => 'Library::Author' );
 }

     use Library::Book_xref;
     my $iterator      = Library::Book_xref->deep_search_where({ 'author_id.last_name'=>'Jordon', 'book_id.book_type'=>'hardback' });

The tricky part is understanding that in deep_search_where you were using the name of the has_a relationship and NOT a table name (seems obvious now).