Accessing data from two or more schemas in a multi tenant application

Question

We are using apartment gem for multi-tenant application. For each account, there will be a separate schema is maintained. based on the subdomain of the account, we list the data in each account.

In our new requirement, there is something like a super account where all the account data can be viewed. For example, if account 1 and account 2 is selected in the user list page, users of account 1 and 2 should be combined and viewed along with search provision. Is there any way to combine more than one schema data in the apartment? or any other alternates

Answer 1

The simplest approach I can think of is to perform union queries across the relevant schemas. So if you have schemas account_1 and account_2, you'd do something like SELECT * FROM account_1.users UNION SELECT * FROM account_2.users.

You can, of course, make this dynamic – so if you send the affected accounts as part of the params hash, you'd potentially have something like this:

accounts = Account.find(params[:account_ids])
# assuming the schema name is stored in the accounts.tenant_name column:
sql = accounts.map { |account| "SELECT * FROM #{account.tenant_name}.users" }.join(" UNION ")
users = User.find_by_sql(sql)