customink / secondbase

Seamless second database integration for Rails.
MIT License
220 stars 31 forks source link

HABTM Support? #35

Closed hut8 closed 2 months ago

hut8 commented 8 years ago

Thanks very much for the gem!

I just moved a table (users) into its own database to facilitate sharing user data between two applications. In my data model, users HABTM organizations. Organizations are specific to each application, so the HABTM table (organizations_users) belongs on the original, non-secondbase database. It looks like secondbase actually causes the query generated to assume that it's in the secondbase. Is there a way to override this?

hmadison commented 8 years ago

IIRC, you need to do has_many ..., through: ... to get this behavior.

metaskills commented 8 years ago

Not sure Secondbase can support this because for a HABTM or even a :through has many to work, you have to do a JOIN and doing that across DBs is simply not going to happen.

hut8 commented 8 years ago

Thanks so much for the quick reply! @metaskills what is the reason that it's not going to happen? I'm using MySQL for this project since at one point it was interfacing with another project, and with MySQL, one can simply fully qualify a table name with the database name and it will work across databases. Before this gem, I've been using a small hack by setting self.table_name = "other_db.#{name.tableize}" which works except for migrations (and probably other things).

metaskills commented 8 years ago

Hey Liam... super familiar with the table name/prefix hacks. Would you mind posting a few lines of code that illustrates your issue. Would be helpful if you gave us models and the instance behavior you are seeing and an example of the logged SQL.

hut8 commented 8 years ago
class User < SecondBase::Base
  has_and_belongs_to_many :organizations
end

class Organization < ActiveRecord::Base
  has_and_belongs_to_many :users
end

In this case, if I do User.first.organizations, it looks for users_organizations in the "second" database. It seems like using has_many :through would do the job; I'll try that later.

zx1986 commented 7 years ago

@hut8 I have the same issue as you. Have you figure out how to do this with secondbase? I am planning to write a scope to do this with raw SQL.

zx1986 commented 7 years ago

But I had try out this, and it works:

class User < ActiveRecord::Base
   has_many :user_tags
   has_many :tags, through: user_tags
end
class UserTag < ActiveRecord::Base
   self.table_name = 'first_db.user_tags'  # The key config line!

   belongs_to :user
   belongs_to :tag
end
class Tag < SecondBase::Base
  self.table_name = 'second_db.tags'  # The `second_db` does not need actually.

  has_many :user_tags
  has_many :users, through: :user_tags
end