[Dbix-class] many_to_many with custom join

[Stephen Shorrock]

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