Using Join Tables in Ruby on Rails

Using join tables in ruby on rails

Yes, this is a many-to-many relationship (class has many students, student has many classes). For this you'll use a has_many :through relation. Take a look at the docs for ActiveRecord::Associations (Ctrl-F for "Association Join Models").

In a migration, t.references :students is how you would specify a belongs_to relation, as it just adds a student_id column (which can only accommodate one id, i.e. one student). A join model, however, will have two columns: student_id and class_id. (Incidentally, calling a model 'Class' in Ruby is asking for trouble. Might I suggest 'Course'?)

What is the use of join table in Rails?

It's standard relational database functionality.

--

Rails is designed on top of a relational database (typically MYSQL or PGSQL), which basically means that you're able to reference "associated" data through the use of foreign keys:

In context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table

---

In the case of Rails, "relationships" in the database are maintained by ActiveRecord - an ORM (Object Relational Mapper). This means that from the application layer, you just have to focus on populating objects:

@user = User.find x
@user.products #-> outputs records from "products" table with foreign key "user_id = x"

ActiveRecord manages your associations, which is why you have to define the belongs_to / has_many directives in your models etc.

Most associations you create will be reference other tables directly:

#app/models/user.rb
class User < ActiveRecord::Base
   has_many :products
end

#app/models/product.rb
class Product < ActiveRecord::Base
   belongs_to :user
end

The problem with this is that it only allows you to associate single records; if you wanted to associate multiple (many-to-many), you need a join table.

---

Rails uses join tables for has_many :through and has_and_belongs_to_many relationships...

Join tables are populated with (at least) the primary key & foreign key of a relationship. For example...

user_id | product_id
   1    |     3
   1    |     5
   2    |     3

This allows you to call:

#app/models/user.rb
class User < ActiveRecord::Base
   has_and_belongs_to_many :products
end

#app/models/product.rb
class Product < ActiveRecord::Base
   has_and_belongs_to_many :users
end

@user.products #-> all products from join table
@product.users #-> all users from join table

In short, if you want to have has_many <-> has_many associations, the join table is necessary to store all the references to the relative foreign keys.

ActiveRecord join tables with Includes

If it is an INNER JOIN you need table1: :table2, assuming your models are correct you need the following

Project.includes(company: :controls)
Project.joins(company: :controls)

how to create a record for a join table?

Your tablenames and your associations in Rails should always be singular_plural with the exception of the odd duckling "headless" join tables used by the (pretty useless) has_and_belongs_to_many association.

class CreateUserBooks < ActiveRecord::Migration[4.2]
  def change
    create_table :user_books do |t|
      t.references :user
      t.references :book
      t.boolean :returned, default: false
    end
  end
end

class UserBook < ActiveRecord::Base
  belongs_to :user
  belongs_to :book
end

class Book < ActiveRecord::Base
    belongs_to :author
    belongs_to :category
    has_many :user_books
    has_many :users, through: :user_books
end

class User < ActiveRecord::Base
    has_many :user_books
    has_many :books, through: :user_books
end

But you should really use a better more descriptive name that tells other programmers what this represents in the domain and not just a amalgamation of the two models it joins such as Loan or Checkout.

I would also use t.datetime :returned_at to create a datetime column that can record when the book is actually returned instead of just a boolean.

If you want to create a join record with any additional data except the foreign keys you need to create it explicitly instead of implicitly (such as by user.books.create()).

@book_user = Book.find(params[:id]).book_users.create(user: user, returned: true)
# or
@book_user = current_user.book_users.create(book: user, returned: true)
# or
@book_user = BookUser.new(user: current_user, book: book, returned: true)

Join tables in ActiveRecord Migrations

Database indexes are not limited to a single column.

t.index [:customer_id, :product_id]

Passing an array creates a compound index which indexes the combination of two columns - which is exactly what a join table is.

This can for example be used to enforce the uniqueness of a combination of values:

t.index [:user_id, :coupon_id], unique: true

Or just speed up queries.

The reason Rails creates two seperate indexes:

# t.index [:customer_id, :product_id]
# t.index [:product_id, :customer_id]

Is that the order actually matters for performance - a grossly simplefied rule of thumb for b-tree indexes is that you should place the most selective column first. You are supposed to choose the best compound index for your use case.

See:

• https://www.postgresql.org/docs/9.1/static/indexes-multicolumn.html
• https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
  -https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_index

Rails: Group By join table

If your only goal is to get the task counts per workspace, I think you want a different query.

@workspaces_with_task_counts = 
  Workspace
    .joins(:projects)
    .joins(:tasks)
    .select('workspaces.name, count(tasks.id) as task_count')
    .group(:workspace_id)

Then you can access the count like this:

@workspaces_with_task_counts.each do |workspace|
  puts "#{workspace.name}: #{workspace.task_count}"
end

EDIT 1

I think this is what you want:

Workspace
  .joins(projects: { tasks: :urgencies })
  .where(urgencies: {urgency_value: 7})
  .group(:name)
  .count

which results in a hash containing all of the workspaces with at least one task where the urgency_value is 7, by name, with the number of tasks in that workspace:

{"workspace1"=>4, "workspace2"=>1}

EDIT 2

SQL is not capable of returning both detail and summary information in a single query. But, we can get all the data, then summarize it in memory with Ruby's group_by method:

Task
  .joins(project: :workspace)
  .includes(project: :workspace)
  .group_by { |task| task.project.workspace.name }

This produces the following data structure:

{
  "workspace1": [task, task, task],
  "workspace2": [task, task],
  "workspace3": [task, task, task, task]
}

But, it does so at a cost. Grouping in memory is an expensive process. Running that query 10,000 times took ~15 seconds.

It turns out that executing two SQL queries is actually two orders of magnitude faster at ~0.2 seconds. Here are the queries:

tasks = Task.joins(project: :workspace).includes(project: :workspace)
counts = tasks.group('workspaces.name').count

The first query gets you all the tasks and preloads their associated project and workspace data. The second query uses ActiveRecord's group clause to construct the SQL statement to summarize the data. It returns this data structure:

{ "workspace1": 3, "workspace2": 2, "workspace3": 4 }

Databases are super efficient at set manipulation. It's almost always significantly faster to do that work in the database than in Ruby.

Rails 4 multi table join with data from all tables

@tests = Test
  .select('tests.*', 'details.detail_text', 'details.detail_error')
  .joins(:run, :detail)
  .where(
    runs: { name: 'SNMSubscriberSpecificTemplatesFeedsTest' },
    tests: { name: 'testSitePrefixFalseForNTINSequentialList' }
  )
  .limit(1000)
  .order(id: :desc)

your views would look like:

<table>
  <tr>
    <th>Test ID</th>
    <th>Test Detail's Detail Text</th>
    <th>Test Detail's Detail Error</th>
  </tr>
  <% @tests.each do |test| %>
    <tr>
      <td><%= test.id %></td>
      <td><%= test.detail_text %></td>
      <td><%= test.detail_error %></td>
    </tr>
  <% end %>
</table>

Recommendation:

• do not use select() explicitly to fetch the "associated values", but instead access them directly through the objects, like below

  @tests = Test
    .joins(:run, :detail).includes(:detail)
    .where(
      runs: { name: 'SNMSubscriberSpecificTemplatesFeedsTest' },
      tests: { name: 'testSitePrefixFalseForNTINSequentialList' }
    )
    .limit(1000)
    .order(id: :desc)
  

  your views would look something like:

  <table>
    <tr>
      <th>Test ID</th>
      <th>Test Detail's Detail Text</th>
      <th>Test Detail's Detail Error</th>
    </tr>
    <% @tests.each do |test| %>
      <tr>
        <td><%= test.id %></td>
        <td><%= test.detail.detail_text %></td>
        <td><%= test.detail.detal_error %></td>
      </tr>
    <% end %>
  </table>
  

---

Related Topics