I want to make sure table join with joining table has strong one-to-one relationship. Following is example Run generator

bundle exec rails g model Page name:string content:text
bundle exec rails g model PageAssociation left_page_id:integer right_page_id:integer

After running these generator, I have to change my page model following

class Page < ApplicationRecord
  has_one :left_page_association, :foreign_key => :left_page_id,
           :class_name => 'PageAssociation'
  has_one :right_page_association, :foreign_key => :right_page_id,
           :class_name => 'PageAssociation'
  has_one :left_page, :through => :left_page_association,
           :source => :right_page
  has_one :right_page, :through => :right_page_association,
           :source => :left_page
end

In my association table model page_association

class PageAssociation < ApplicationRecord
  belongs_to :left_page, class_name: 'Page'
  belongs_to :right_page, class_name: 'Page'
end

Now using rails c I can do following

page_one = Page.create(name: 'first page', content: 'first page content')
page_two = Page.create(name: 'second page', content: 'second page content')
PageAssociation.create(left_page: page_one, right_page: page_two)
page_one.left_page
page_two.right_page

All working fine and it is returning pages. but I can do also this

page_three = Page.create(name: 'third page', content: 'third page content')
PageAssociation.create(left_page: page_one, right_page: page_three)

Of course it still shows on relationship due to has_one but it is creating another relationship. So I want following

1. Only one association can be created and it should not allow another one
2. What is best way to do this as we have million of records
3. Is it one to use page.left_page? or is there any other optimised way to do same thing.
4. Should I add indexing using following migration lines. Does it effect performance.

add_foreign_key :page_associations, :pages, column: :left_page_id
add_foreign_key :page_associations, :pages, column: :right_page_id

I changed migration to make column value unique, so it now give error when I create another PageAssociate with same page.id but is it right way?

      t.integer :left_page_id, null: false, index: { unique: true }
      t.integer :right_page_id, null: false, index: { unique: true }

what problem I am solving

So I have books table where I have multiple books, but some books borrow pages from one another. So suppose I have book-A and book-B so I want to show relationship of book-a to book-b. If they are related then I will create another relationship of book-a page-10 linked to book-b page 20, so when I click on book-a to synchronise, it will bring all changes of book-b page 20 in my book-a. So above is my first step to connected two books. I understand solving it using self join and keys is best but I can't do as we have huge number of records so that is not possible. So I have used above method to do it. But I did add unique constraints on db and validations in model to solve it. but I feel it is still not good way of doing it. later one I will make following

main-book , main-book-page, secondary-book-page

This will allow me to bound later pages each other. The name book and pages are fictions, actually entities are different.

CodePudding user response：

What you want here is really just a normal many to many table setup:

class Book < ApplicationRecord
  has_many :book_pages
  has_many :pages, through: :book_pages
end

class Page < ApplicationRecord
  has_many :book_pages
  has_many :books, through: :book_pages
end

class BookPage < ApplicationRecord
  belongs_to :book
  belongs_to :page
  validates_uniqueness_of :book_id, scope: :page_id
end

Uniqueness in this case can be guarenteed by adding a unique index:

add_index :book_pages, [:book_id, :page_id]

Why?

M2M join table setups where you have two foreign keys that are assigned arbitrarily are not a very good design and don't work well with ActiveRecord since you can't define assocations with an OR clause.

Each association can only have a single foreign key. This means that you can't treat it as a homogenius collection and cannot eager load it.

That means you need to write crappy joins like this instead of being able to work with a proper assocation:

Book.joins(
  "LEFT JOIN pages_assocations pa ON pa.left_page_id = books.id OR pa.left_page_id = books.id"
)

And you also have to write steaming piles when creating indirect assocations.

While the table setup with a single row per book/page combo may seem to require more rows on the onset its also much more flexible as you can map out the assocations between books by subqueries, lateral joins or grouping and counting the number of matches.

class Book < ApplicationRecord
  has_many :book_pages
  has_many :pages, through: :book_pages

  def books_with_pages_in_common
     Book.where(
        id: BookPage.select(:book_id)
                    .where(page_id: pages)
     )
  end
end