[Dbix-class] Need help with a search query
Frank Schwach
fs5 at sanger.ac.uk
Fri Jun 15 08:23:34 GMT 2012
try this:
my $rs = $schema->resultset('Person')->search(
{
'people_companies.code' => $code,
},
{
join => 'people_companies',
}
);
your relationship is called "people_companies", so your first attempt
was correct but the "where" clause referred to relationship "company",
which doesn't exist. If you want to search for something in "company"
across the PeopleCompany bridge model, follow Hailin's advice and set up
a many-to-many relationship from Person to Company.
Hope that helps
Frank
On 15/06/12 03:42, Dennis Daupert wrote:
> For some reason the right search query syntax has been elusive.
>
> I'm trying to get a list of people belonging to a particular company
> where the company has a particular 'code' value.
>
> ==============================
> Sample query code attempt:
> ------------------------------
> sub get_ppl_by_org_code {
> my ( $schema, $code ) = @_;
>
> my $rs = $schema->resultset('Person')->search(
> {
> 'company.code' => $code,
> },
> {
> join => [qw/ company /], # also tried people_companies
> }
> );
>
> return( $rs );
> }
>
> Gives error: "No such relationship company on Person"
> ==============================
> Schemas: (produced by DBIx::Class::Schema::Loader)
> ------------------------------
> __PACKAGE__->table("people");
> __PACKAGE__->add_columns(
> "agent_id",
> { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
> "first_name",
> { data_type => "varchar", is_nullable => 1, size => 40 },
> "last_name",
> { data_type => "varchar", is_nullable => 1, size => 40 },
> <snip>
>
> __PACKAGE__->has_many(
> "people_companies",
> "DB::Schema::Result::PeopleCompany",
> { "foreign.agent_id" => "self.agent_id" },
> { cascade_copy => 0, cascade_delete => 0 },
> );
> ==============================
> __PACKAGE__->table("group_company_xl");
> __PACKAGE__->add_columns(
> "company_id",
> { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
> "company",
> { data_type => "varchar", is_nullable => 0, size => 50 },
> "service_center",
> { data_type => "varchar", is_nullable => 1, size => 20 },
> "code",
> { data_type => "varchar", is_nullable => 1, size => 5 },
> <snip>
>
> __PACKAGE__->has_many(
> "people_companies",
> "DB::Schema::Result::PeopleCompany",
> { "foreign.company_id" => "self.company_id" },
> { cascade_copy => 0, cascade_delete => 0 },
> );
>
> Seems odd, DBIx::Class::Schema::Loader produced accessor
> with same name as the one for people table. Is that kosher?
> ==============================
> __PACKAGE__->table("people_companies");
> __PACKAGE__->add_columns(
> "agent_id",
> { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
> "company_id",
> { data_type => "integer", is_foreign_key => 1, is_nullable => 0 },
> );
>
> __PACKAGE__->belongs_to(
> "agent",
> "DB::Schema::Result::Person",
> { agent_id => "agent_id" },
> { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
> );
>
> __PACKAGE__->belongs_to(
> "company",
> "DB::Schema::Result::GroupCompanyXl",
> { company_id => "company_id" },
> { is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
> );
> ==============================
>
> Can someone help me learn the secret handshake?
>
> /dennis
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
More information about the DBIx-Class
mailing list