Re: [Dbix-class] Need help with a search query
Thank you Hailin Hu and Frank Schwach. I appreciate
the info, and what's left of my hair does also :-)
The multiple level join did work!
join => {"people_companies" => "company"}
During doc search yesterday (hours, frankly),
I read a statement somewhere that a many_to_many
accessor cannot be used in a join. Even so, I did
try it, but it didn't work for me.
This join also did not work for me, don't know why:
join => 'people_companies',
When trying that join, I did also set 'people_companies.code'
in the where clause, to no avail. I also tried using the
belongs_to 'company' accessor out of desperation, or
maybe a moment of madness.
Anyways, again, thanks guys, maybe I can keep my job now :-)
/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
Re: [Dbix-class] Need help with a search query
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 },
__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 },
__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.
___
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
Re: [Dbix-class] Need help with a search query
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 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 },
>
>
> __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 },
>
>
> __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
___
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
