Rails joins wrong tables in shards-CodePudding

I'm having an following database structure, with the schema cloned to 2 databases:

users (id, name)
todos (id, title)
todos_users (user_id, todo_id)

I'm having 2 different clients in my software, Big company and Small company. The shards represent the companies' own todos, and don't wanna get separated. All users, including Small company users are stored in the Big company database. This is because we don't have separated logins, and some users can use both Big and Small company todo-lists.

We're having a following problem though, with these models:

# ApplicationRecord connects to the current shard the user is logged to
# GlobalRecord always uses the Big company -database

class User < GlobalRecord
end

class Todo < ApplicationRecord
  has_and_belongs_to_many :users
end

Resulting queries for example DatabaseProxy.on_shard(shard: :small_company) do Todo.first.users end

SELECT "todos".* FROM "todos" ORDER BY "todos"."id" ASC LIMIT $1  [["LIMIT", 1]]
SELECT "users".* FROM "users" INNER JOIN "todos_users" ON "users"."id" = "todos_users"."user_id" WHERE "todos_users"."todo_id" = $1

Here is the problem; it joins the todos_users to users in the primary shard! This is problematic, as the todos don't lay in the Big company database but rather in the Small one. Therefore all the information I insert into the Small company database todos_users gets lost when selecting the todos.

I'm using Rails 6.1, but also upgrading to 7 can be possible.

CodePudding user response：

Solved by upgrading to Rails 7 (disable_joins was introduced in 7) and changing has_and_belongs_to_many to:

# todos_user.rb
class TodosUser < ApplicationRecord
  belongs_to :user
  belongs_to :todo
end

# todo.rb
class Todo
  has_many :todos_users
  has_many :users, through: :todos_users, disable_joins: true
end