NAME

Class::DBI::Plugin::DeepAbstractSearch - deep_search_where() for Class::DBI

SYNOPSIS

        use base 'Class::DBI';
        use Class::DBI::Plugin::DeepAbstractSearch;

        my @cds = Music::CD->deep_search_where(
                {
                        'artist.name' => $artist_name
                }
        );

DESCRIPTION

This plugin provides a SQL::Abstract search method for Class::DBI. It is similar to Class::DBI::AbstractSearch, but allows you to search and sort by fields from joined tables.

Note: When searching and sorting by the fields of the current class only, it is more efficient to use Class::DBI::AbstractSearch.

METHODS

deep_search_where

        my @cds = Music::CD->deep_search_where(
                {
                        'artist.name' => $artist_name
                }
        );

This method will be exported into the calling class, and allows for searching of objects using SQL::Abstract format based on fields from the calling class as well as using fields in classes related through a (chain of) 'has_a' relationships to the calling class.

When specifying a field in a related class, you separate it with a period from the corresponding foreign key field in the primary class.

        package Music::Artist;
        use base 'Class::DBI';
        Music::Artist->table('artist');
        Music::Artist->columns(All => qw/artistid name/);
        Music::Artist->has_many(cds => 'Music::CD');

        package Music::CD;
        use base 'Class::DBI';
        Music::CD->table('cd');
        Music::CD->columns(All => qw/cdid artist title year/);
        Music::CD->has_many(tracks => 'Music::Track');
        Music::CD->has_a(artist => 'Music::Artist');

        package Music::Track;
        use base 'Class::DBI';
        Music::Track->table('track');
        Music::Track->columns(All => qw/trackid cd position title/); 

        ## Tracks on all CDs with the title "Greatest Hits"
        @tracks = Music::Track->deep_search_where(
                {
                        'cd.title' => "Greatest Hits"
                },
                {
                        sort_by => 'cd.title'
                }
        );

        ## Tracks on CDs by Willie Nelson, sorted by CD Title and Track Position
        @tracks = Music::Track->deep_search_where(
                {
                        'cd.artist.name' => "Willie Nelson"
                },
                {
                        sort_by => 'cd.title, position'
                }
        );

        ## First 3 Tracks on CDs, whose title contains "Outlaw", by Willie Nelson
        @tracks = Music::Track->deep_search_where(
                {
                        'cd.artist.name' => "Willie Nelson",
                        'cd.title' => { -like => '%Outlaw%' },
                        position => { '<=' => 3 }
                },
                {
                        sort_by => 'cd.title, position'
                }
        );

count_deep_search_where

        my $num_cds = Music::CD->count_deep_search_where(
                {
                        'artist.name' => $artist_name
                }
        );

This method will be exported into the calling class, and allows for counting of objects using SQL::Abstract format based on fields from the calling class as well as using fields in classes related through a (chain of) 'has_a' relationships to the calling class.

get_deep_where

    my ($what, $from, $where, $bind) = $class->get_deep_where($where, $attr);

This method will be exported into the calling class, and allows for retrieving SQL fragments used for creating queries. The parameters are the same as to deep_search_where.

AUTHOR

Stepan Riha, sriha@cpan.org

COPYRIGHT

Copyright (C) 2005, 2007, 2008 Stepan Riha. All rights reserved.

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

SEE ALSO

Class::DBI, SQL::Abstract, Class::DBI::AbstractSearch