0

I have a join table joining a Client and a Setor (plural: setores) models. The relation is that a Client has_and_belongs_to_many Setores (there are three Setores, a Client can have from one to all three of them. Setores have lots of Clients).

My problem is: In this join table I added a reference to the User model. Setores have many Users, but a relation between one client and one setor have only one user. But I don't know how to read and write this association on the clients_setores table.

My models are as follows:

class Client < ActiveRecord::Base
has_and_belongs_to_many :documents
has_and_belongs_to_many :setores
has_many :screenings
has_many :contacts
has_many :interactions, through: :contacts
validates :cnpj, uniqueness: true


class Setor < ActiveRecord::Base
  self.table_name = 'setores'
  has_many :documents
  has_many :screenings
  has_many :users
  has_and_belongs_to_many :clients
  attr_acessor :user_id


class User < ActiveRecord::Base
has_one :setores
has_many :clients

And the current join tables parameters that have been working appear like this on the end of Clients controller:

  private
# Use callbacks to share common setup or constraints between actions.
def set_client
  @client = Client.find(params[:id])
end

# Never trust parameters from the scary internet, only allow the white list through.
def client_params
  params.require(:client).permit(:cnpj, :pacote, :razsoc, :vencimento, user_ids:[], document_ids:[], setor_ids:[])
end

Note that "user_ids:[]" was my attempt to get it to work, which has failed so far.

In the views I use the current join tables like this (taken from /client/_form.html.erb):

<%= f.collection_check_boxes :setor_ids, Setor.where(pacote: true), :id, :setor do |b| %>

So with these checkboxes I can create an entry in the clients_setores table.

What I want to do is to be able to pick a User that belongs to a given setor_id from a drop down menu and store this relation in the join table. I did manage to make such menu appear with the following code, in the same _form.html.erb:

<%= f.collection_select :user_ids, User.where(:setor_id => 1), :id, :email %>

But when I submit the form, the values are not saved. I don't know if my problem is just that I didn't get the right way to record this association on my view or if my problem is further down in the controller (probably) and model (maybe).

Closest problem I found in SO was Rails 4 Accessing Join Table Attributes, but the association type is different (has_many/through) and there isn't a third relation involved, so I couldn't figure out how to make it work for me.

Community
  • 1
  • 1
Sam Rodrigues
  • 255
  • 1
  • 5
  • 13
  • Hey I didn't fully understand your question so if you could clarify what you mean by "My problem is: In this join table I added a reference to the User model. " The join table for a has_and_belongs_to_many only allows for two columns (the foreign keys to your setor and client table) so if you're saying you wanted to add a reference to that table you'll have to use a "has_many through" setup. Also, you didn't put that your client belongs to a user in your Client model. – bkunzi01 Mar 29 '16 at 22:16
  • For a Client to have many Users, it would have to be through the same number of Setores (1 setor : 1 client; 3 setores: 3 clients), does the has_many/through relation work like this? As for the Client belonging to the User, it always belongs to as many Users as Setores; is this relevant? – Sam Rodrigues Mar 29 '16 at 22:25
  • Ok so you're saying if a client is to have one user, he must also have one setore? Sorry I'm still not sure what you're trying to do but if you need a way to make sure that clients and setores also know about Users at the same time, you will need to use a "has_many through" association so that you can add additional columns to that join table to store the extra info on users. – bkunzi01 Mar 29 '16 at 22:27
  • Exactly. In the logical order of this system, a Client must first be assigned a Setor, only then it can have an User (from said Setor) assigned. (Setor means "department". If my client needs the service of, say, dept. of accounts payable, it gets a helper from this department) – Sam Rodrigues Mar 29 '16 at 22:30

2 Answers2

1

Example of many-to-many associations between three models, done with a single join table:

I start out by generating some models: 

rails g model User; rails g model Setor; rails g model Client;
rails g model Joiner user_id:integer setor_id:integer client_id:integer

By the way, references is a way to add a foreign key that refers to an existing model. I.e. user:references will create a user_id column. It also adds an "index" to the foreign key, which improves the performance.

Then I add some associations to the classes

class Joiner
  # columns: user_id, setor_id, client_id
  belongs_to :user
  belongs_to :setor
  belongs_to :client
end

class User
  has_many :joiners
  has_many :setors, through: :joiners, source: :setor
  has_many :clients, through: :joiners, source: :client
end

class Setor
  has_many :joiners
  has_many :users, through: :joiners, source: :user
  has_many :clients, through: :joiners, source: :client
end

class Client
  has_many :joiners
  has_many :users, through: :joiners, source: :user
  has_many :setors, through: :joiners, source: :setor
end

With this code written, you have many-to-many associations for the three models.

You can then write:

User.create; Setor.create;
Joiner.create(user_id: User.first.id, setor_id: Setor.first.id);
User.first.setors.length # => 1

This won't work for self-joins (i.e. a nested comment system), by the way, but that's not part of the question.

max pleaner
  • 26,189
  • 9
  • 66
  • 118
  • In this case, where would I store the relation between a Client and his (up to three) UserSetor pairings? – Sam Rodrigues Mar 30 '16 at 17:55
  • @SamOliver probably another join table. `ClientUserSetor` perhaps. It's not pretty, but it'll do the trick. By the way, look at the [detailed association reference](http://guides.rubyonrails.org/association_basics.html). – max pleaner Mar 30 '16 at 18:21
  • I read your first answer a few times and it gave me the idea of creating a new model, say "WeirdRelation", with a table that would store only the user_id, setor_id and user_id. This is not how I chose to solve this particular problem (see my answer below), but that's what I'd go with if I had more "Setores" per Client. – Sam Rodrigues Mar 30 '16 at 18:33
  • ok, that approach will work if you have a really simple case like this but im guessing the only reason you're doing that is because you don't really understand how to put together many-to-many relationships. Take a look at other stackoverflow questions on the topic. – max pleaner Mar 30 '16 at 19:03
  • @SamOliver I've re-done my answer to give you a more concrete example. Hope it's clear. – max pleaner Mar 30 '16 at 20:17
0

What I ended up doing to achieve the desired effect was to add three columns to the Client model:

userset1

userset2

userset3

This way a Client has up to three (since the field can be blank) relations with users, according to the Setores (departments) he has at his service.

This is the easiest solution in this case since I only have 3 Setores. It would not be for larger numbers.

If I had more than 3 Setores, making it impractical to keep adding columns in the Client table, I would cerate a new model just to store the relations, in this case, a table that would have, besides its unique ID, client_id, setor_id and user_id.

Sam Rodrigues
  • 255
  • 1
  • 5
  • 13