in Rails, need to set many-to-many relationship and one-to-many relationships between the same two models

Question

I have an app in which projects belong to one owner (a user) and have and belong to many members (users).

I have the following code set up in the models to handle these associations. Note, project has owner_id in its database but not member_id

project model code

attr_accessible :description, :name, :owner_id, :avatar_url

belongs_to :owner, foreign_key: :owner_id, class_name: "User"
has_and_belongs_to_many :members, class_name: "User"

user model code

has_and_belongs_to_many :projects
has_many :owned_projects, class_name: "Project", foreign_key: "owner_id"
has_many :associated_projects, class_name: "Project", foreign_key: "member_id"

On a given project, I can call p.owner but not p.members. And I for a user I can call u.owned_projects or u.projects but not u.associated_projects.

When I try those commands, I get the following errors

1.9.3p194 :003 > p.members
User Load (0.2ms)  SELECT "users".* FROM "users" INNER JOIN "projects_users" ON "users"."id" = "projects_users"."user_id" WHERE "projects_users"."project_id" = 3
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such table: projects_users: SELECT "users".* FROM "users" INNER JOIN "projects_users" ON "users"."id" = "projects_users"."user_id" WHERE "projects_users"."project_id" = 3

and

1.9.3p194 :007 > ryan.associated_projects
Project Load (0.2ms)  SELECT "projects".* FROM "projects" WHERE "projects"."member_id" = 14593
ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: projects.member_id: SELECT "projects".* FROM "projects"  WHERE "projects"."member_id" = 14593

How can I set up these models so they'll work as I intend?

Note: I modeled my code after - Two has_many links between the same models but it hasn't helped.

do you have a intermediate table in your database to combine the Member and the Project? — Zippie

Zippie Zippie · Accepted Answer · 2013-03-17T10:54:26

You have to have a has_many_and_belongs_to relationship in your User and Project models. Also you need a intermediate table inbetween. http://guides.rubyonrails.org/association_basics.html#the-has_and_belongs_to_many-association

Class Project < ActiveRecord::Base

     attr_accessible :description, :name, :owner_id, :avatar_url

     belongs_to :owner, foreign_key: :owner_id, class_name: "User"
     has_and_belongs_to_many :members, class_name: "User"

end

Class User < ActiveRecord::Base

     has_and_belongs_to_many :projects
     has_many :owned_projects, class_name: "Project", foreign_key: "owner_id"

end

As said, you will have to have a middle table between member Project and User(member). You had that error when you tried to see project members: no such table: projects_users

Projects_Users Table
user_id | project_id

in Rails, need to set many-to-many relationship and one-to-many relationships between the same two models

1 Answers