[Dbix-class] Re: many_to_many with custom join

[Stephen Shorrock]

Hi Sorry my fault I've noticed the missing -ident=> in the relationship:

 __PACKAGE__->has_many('user_groups' =>
'ROFF::schema::Result::VisaUserGroup', sub { my $args=shift; return {
"$args->{foreign_alias}.vug_type" => {'='=>'User'},
"$args->{foreign_alias}.vug_vig_id" =>
{-ident=>"$args->{self_alias}.vig_id" } }  }); # user members

Stephen

On Mon, Dec 3, 2012 at 1:08 PM, Stephen Shorrock
<stephen.shorrock at gmail.com> wrote:
> Hi
>
> I can't fathom this out and was wondering whether anyone is able to
> give advice, or perhaps highlight the obvious mistake?!
>
> I've built a many_to_many relationship with a custom join, where the
> SQL seen by DBIC_TRACE looks correct and when pasted into mysql (with
> parameters also copied) returns the correct values.  But when calling
> within my application using something like:
>
> my $group = <is a ROFF::schema::Result::VisaGroup object>
> my @arr = $group->users();
>
> returns an empty array (@arr)
>
> However:
> my @arr = $group->members();
>
> returns the correct set of results users are a subset of (all) members.
>
> The respective SQL statements are:
>
> For users(): SELECT user.vat_id, user.vat_stf_id, user.vat_crs_id,
> user.vat_name FROM visa_groups visagroup__row  JOIN visa_user_groups
> me ON ( me.vug_type = ? AND me.vug_vig_id = ? )  JOIN
> visa_attendance_users user ON user.vat_id = me.vug_vat_id WHERE (
> visagroup__row.vig_id = ? ): 'User', 'visagroup__row.vig_id', '1'
>
> and for members() SELECT user.vat_id, user.vat_stf_id,
> user.vat_crs_id, user.vat_name FROM visa_user_groups me  JOIN
> visa_attendance_users user ON user.vat_id = me.vug_vat_id WHERE (
> me.vug_vig_id = ? ): '1'
>
> and the classes have been setup as:
>
> package ROFF::schema::Result::VisaAttendanceUser;
>
> __PACKAGE__->add_columns(
> ...
>    "vat_id"
> ...)
> __PACKAGE__->set_primary("vat_id");
>
>
> package ROFF::schema::Result::VisaUserGroup;
> ....
> __PACKAGE__->add_columns(
> ...
>    "vug_type"...
>    "vug_vat_id"..
>    "vug_vig_id"...
> ...)
>
> __PACKAGE__->belongs_to('user'=>'ROFF::schema::Result::VisaAttendanceUser','vug_vat_id'
> );
>
>
> package ROFF::schema::Result::VisaGroup;
>
> __PACKAGE__->add_columns(
> ...
>    "vig_id"...
> )
>
> __PACKAGE__->set_primary("vig_id");
>
>
> __PACKAGE__->has_many('user_groups' =>
> 'ROFF::schema::Result::VisaUserGroup', sub { my $args=shift; return
> {"$args->{foreign_alias}.vug_type" => {'='=>'User'},
> "$args->{foreign_alias}.vug_vig_id" => "$args->{self_alias}.vig_id" }
> }); # user members
> __PACKAGE__->has_many('member_groups' =>
> 'ROFF::schema::Result::VisaUserGroup',"vug_vig_id"); # all members
> __PACKAGE__->many_to_many('users' => 'user_groups', 'user');
> __PACKAGE__->many_to_many('members' => 'member_groups', 'user');
>
> Am I using the relationships, correctly, as intended?
>
> Thanks in advance
>
> Stephen