The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.

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