mysql - Ruby/DataMapper: Problems with multiple many-to-many associations -

i have implemented models user-group relationship, 1 user can member , owner of 1 or more groups , 1 group can have 1 or more owners , 1 or more members.

now, problem user.owned_groups returns 1 group if user owns more 1 group.

i facing same issue user.member_groups, group.owners , group.members. methods returning 1 record.

below models , values in database.

models: -

require 'rubygems' require 'data_mapper' require 'dm-types'  module contacts   class user     include datamapper::resource      property :id,         serial,   :key => true     property :username,   string,   :required => true, :unique => true      has n, :group_owners, :child_key => [:owned_group_id]     has n, :owned_groups, 'group', :through => :group_owners      has n, :group_members, :child_key => [:member_group_id]     has n, :member_groups, 'group', :through => :group_members      #------------------------------------------#     # args should array of hashes        #     # example: args = [{:id => 1},{:id => 2}]  #     #------------------------------------------#     def self.get_users args       users = array.new       args.each |user|         all_users = user.all(user)         all_users.each |u|           user_hash = u.attributes            user_hash[:owned_groups] = array.new           u.owned_groups.each |owned_group|             g = owned_group.attributes             user_hash[:owned_groups].push g           end            user_hash[:member_groups] = array.new           u.member_groups.each |member_group|             g = member_group.attributes             user_hash[:member_groups].push g           end            users.push user_hash         end       end       return users     end   end    class group     include datamapper::resource      property :id,   serial, :key => true     property :name, string, :required => true, :unique => true      has n, :group_owners, :child_key => [:owner_id]     has n, :owners, 'user', :through => :group_owners      has n, :group_members, :child_key => [:member_id]     has n, :members, 'user', :through => :group_members      #------------------------------------------#     # args should array of hashes        #     # example: args = [{:id => 1},{:id => 2}]  #     #------------------------------------------#     def self.get_groups args       groups = array.new        args.each |group|         all_groups = group.all(group)         all_groups.each |g|           group_hash = g.attributes            group_hash[:owners] = array.new           g.owners.each |owner|             u = owner.attributes             group_hash[:owners].push u           end            group_hash[:members] = array.new           g.members.each |member|             u = member.attributes             group_hash[:members].push u           end           groups.push group_hash         end       end       return groups     end   end    class groupowner     include datamapper::resource      property :id, serial, :key => true     belongs_to :owner, 'user'     belongs_to :owned_group, 'group'   end    class groupmember     include datamapper::resource      property :id, serial, :key => true     belongs_to :member, 'user'     belongs_to :member_group, 'group'   end end 

mysql: -

mysql> select * contacts_users; +----+-----------+ | id | username  | +----+-----------+ |  1 | testuser1 | |  2 | testuser2 | +----+-----------+  mysql> select * contacts_groups; +----+------------+ | id | name       | +----+------------+ |  1 | testgroup1 | |  2 | testgroup2 | +----+------------+ 2 rows in set (0.00 sec)   mysql> select * contacts_group_owners; +----+----------+----------------+ | id | owner_id | owned_group_id | +----+----------+----------------+ |  1 |        1 |              1 | |  2 |        2 |              1 | |  3 |        1 |              2 | |  4 |        2 |              2 | +----+----------+----------------+ 4 rows in set (0.00 sec)  mysql> select * contacts_group_members; +----+-----------+-----------------+ | id | member_id | member_group_id | +----+-----------+-----------------+ |  1 |         1 |               1 | |  2 |         2 |               1 | |  3 |         1 |               2 | |  4 |         2 |               2 | +----+-----------+-----------------+ 4 rows in set (0.00 sec) 

in debug mode, mysql query generated user.owned_groups:

    select        `contacts_groups`.`id`,        `contacts_groups`.`name`,        `contacts_groups`.`created_at`,        `contacts_groups`.`updated_at`     `contacts_groups`     inner join `contacts_group_owners`        on `contacts_groups`.`id` = `contacts_group_owners`.`owned_group_id`     inner join `contacts_users`        on `contacts_group_owners`.`owned_group_id` = `contacts_users`.`id`     `contacts_group_owners`.`owned_group_id` = 1     group        `contacts_groups`.`id`,        `contacts_groups`.`name`,        `contacts_groups`.`created_at`,        `contacts_groups`.`updated_at`     order `contacts_groups`.`id 

versions:

ruby - 2.0.0-p0

sinatra - 1.3.4

datamapper - 1.2.0

am missing out trivial?

thanks in advance.

try this:

   select        *           `contacts_groups`     inner join `contacts_group_owners`        on `contacts_groups`.`id` = `contacts_group_owners`.`owned_group_id`     inner join `contacts_users`        on `contacts_group_owners`.`owner_id` = `contacts_users`.`id`            `contacts_group_owners`.`owned_group_id` = 1