[Dbix-class] Need help with a search query

[Hailin Hu]

just as the error said, there is no such a relationship.

try to define a many-to-many relationship
http://search.cpan.org/~arodland/DBIx-Class-0.08196/lib/DBIx/Class/Relationship.pm#many_to_many

or use a multiple level join
join => {"people_companies" => "company"}

On Fri, Jun 15, 2012 at 11:42 AM, Dennis Daupert <ddaupert at sbcglobal.net> 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