Many to Many Table with an Extra Column in Rails

Many to many table with an extra column in Rails

User and Event have many-to-many relationship, you can not set up this association with just 2 model, you must have the join model, or join table.

In your case, you added attribute confirmed, so you will need a join model, named Confirmation (as other people recommended). Your define associations will be like this:

class User
  has_many :events, through: :confirmations
  has_many :confirmations
end

class Event
  has_many :users, through: :confirmations
  has_many :confirmations
end

class Confirmation
  belongs_to :user
  belongs_to :event
end

Rails: How to query based on an extra field in a many-to-many relationship?

You can do this by joining the association to the query, and then searching in the scope of the associated table:

User.joins(:groupizations).where(:groupizations => {:active_user => true})

Note that this will return duplicate rows if multiple groupizations meet this criteria. To get distinct users you can do one of the following:

Add a call to .select("DISTINCT users.*") with some ORDER clause as .order("users.id") (change the sort column as needed)
Add a GROUP BY clause like this: .group("users.id"). For non-MySQL databases, you may need to be more specific and include all columns in the grouping:
.group(User.column_names.map{|c| "users.#{c}"}.join(','))

Adding an extra field to the join table in has_many through association

First of all:

there is a mistake in the JobReview model. Replace belongs_to :job_review with belongs_to :review
the job_review_params method should be renamed to job_params as it is in fact a Job object that you assign params to.

When you assign reviews to a job passing the review_ids parameter, Rails tries to automatically create the job_reviews association. It fails because Rails can't automatically calculate the user_id value and it is not passed properly.

Although you have the user_id parameter in the form, it is passed as job's attribute. Rails doesn't know what to do with it.

One of the ways to solve the problem is to assign reviews to a job manually:

job_params[:review_ids].each do |review_id|
  @job.job_reviews.build(review_id: review_id, user_id: current_user.id)
end

@job.save

In this case you don't have to send user_id through the form because it is available in the controller:

= simple_form_for @job do |f|
  = f.association :reviews, collection: Review.all, as: :check_boxes, include_hidden: false, label: false
  = f.button :submit, class: 'btn btn-success'

Updating extra column in a has_many, :through table with Coffeescript

Because I struggled for a long time with this I'm putting my stupidly simple solution here. Hopefully it helps someone else.

It's as simple as deleting the old join table records on update before the changes are saved, i.e.:

docs_controller.rb

def update
  @doc = Doc.find(params[:id])
  @doc.publications.destroy_all
  respond_to do |format|
    if @doc.update_attributes(params[:doc])
      format.html { redirect_to share_url(@doc) }
      format.json { head :no_content }
    else
      format.html { render action: "edit" }
      format.json { render json: @doc.errors, status: :unprocessable_entity }
    end
  end
end

Boom.

in Rails how to select additional columns from multiple tables

result = User.first.usertags.joins(:tag).select("tags.name as tag_name, tags.id, usertags.id, user.id")

result.collect{|r| r.tag_name}

has_many through with an extra column

I think the example you gave of your Groupship class may be slightly wrong. Shouldn't it be..

class Group < ActiveRecord::Base
  has_many :groupships
  has_many :users, through: :groupships
end

A group can have many users and a user can have many groups right?

As for the view example you gave, you can't simply call group.groupships.owner because group.groupships is a collection, and the owner boolean is a property of a single groupship. You could do something like this instead:

<% @groups.each do |group| %>
  <div class="well well-small">
    <h3><%= group.name %></h3>
    Owners:
    <%= group.groupships.each do |groupship| %>
      <%= groupship.user.name %>:
      <%= groupship.owner? %>
    <% end -%>
  </div>
<% end %>

Seeking help to prevent adding table with too many columns

when mapping OO inheritance to relational database we traditionally have three ways of doing it (as highlighted in this link):

Single Table Inheritance(STI): One table with all the attributes used by all the subclasses. This way is more suitable for cases where the attributes don't change much from one subclass to another.
Class Table Inheritance(MTI): One dad table with common attributes, and child tables for each subclass with their specific attributes and a foreign key to the dad table. Good when there are many different attributes between each subclass and even different associations.
Concrete Table Inheritance: One table for each class with all the needed attributes. May be good if there are little common attributes.

Rails allow us to create a Single Table Inheritance(STI) and we can also use the Concrete Table Inheritance. But it doesn't have a built in way of dealing with the Class Table Inheritance(known as well as Multiple Table Inheritance - MTI), what doesn't prevents you from creating your own way of doing it, like it is done in this post.

Rails also allow us to have a Polymorphic Association, which is useful when we want an association of one type for certain records and of another for others, this can be used for inheritance, but was not actually designed for this. In this link you will find an example of all the three types and how to use them.

Another way to achieve it is by having an options jsonb field. This is a good approach when you have unpredictable/changeable fields that will vary from one record to another. Which is a much simpler way but must be well designed, cause may have some downsides.

Proposed Solution:

I wouldnt use:

STI: as you said it is going to be a lot of different management systems, I believe it is not a good idea because you would have too many unused columns.
Concrete Table Inheritance: I guess you want to make it easy to add new management systems as you go, treating them as one and ignoring their nuances. So it is not really a good option because it would make it harder to understand their commonalities.

I could use:

MTI: it would be a good database design, but as youre using rails you would have to deal with it your own way and it could add some complexity to the code. I would use it if I were more concerned about having a normalized database.
Polymorphic Association: would add some complexity to the database when compared to the MTI and to make it usable as an inheritance would add some complexity to the code as well. I prefer using it with an actual polymorphic association.

I would use:

An options json field: I believe you will need to access this specific attributes just in some specific moments and that each different management system wouldn't have other associations. So I guess it would be the most straightforward solution being totally supported by both rails and the database without adding complexity to neither one.

Ps: If you have just some little different attributes, I would use a STI instead.

Many to Many Table with an Extra Column in Rails