[Dbix-class] Need help with a search query

[Frank Schwach]

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.