On Monday, June 12, 2017 at 2:00:02 PM UTC-7, [email protected] wrote:
>
> I'm working on a project in a multi-tenant setting, meaning that most of
> our tables have an extra column `tenant_id` to denote which tenant the
> record belongs to. This field is denormalized across multiple records, so a
> table with a `tenant_id` column could have a foreign-key reference to
> another table with a `tenant_id`. Obviously we run the risk of getting
> inconsistent data, but this isn't a major concern. The gains we get by
> prefixing our indices with `tenant_id` and always filtering by `tenant_id`
> outweigh any potential negatives.
>
> However, we aren't able to take advantage of the `tenant_id` in our
> associations. Suppose we use the example of `albums` and `artists` used in
> the Associations guide, but we add a `tenant_id` column to both of the
> tables. This `tenant_id` isn't part of any composite key.
>
> When fetching the albums associated with a single artist (`artist.albums`)
> we'd like the generated query to be `SELECT * FROM albums WHERE artist_id =
> ? AND tenant_id = ?`, where it grabs the `tenant_id` from the artist.
>
You can use an association block for this:
Artist.one_to_many :albums do |ds| ds.where(:tenant_id=>tenant_id) end
> I understand that this raises some questions when doing eager loading:
> What if the original dataset contains records from multiple tenants? I
> imagine we'd do `WHERE artist_id IN (?, ?, ?) AND organization_id IN (?, ?,
> ?)`. I would expect Sequel to then wire-up the associations while ignoring
> the `tenant_id`, and simply avoid addressing the issue of potential data
> inconsistencies.
>
To make eager loading use a different block than regular loading, you could
try:
Artist.one_to_many :albums, :eager_block=>proc{|ds| ds} do |ds|
ds.where(:tenant_id=>tenant_id)
end
>
> What would be the best way to get this behavior from Sequel? We also have
> a few tables that are partially global: Some records have `tenant_id` set
> to null and are visible to everyone, but other records that do have it set
> are only visible to that particular tenant. If there's a solution that
> could also handle this case that would be fantastic.
>
>
>
> And, related, if there's a higher level solution for multi-tenant
> settings, that's be great. I'd love to be able to say
> `Sequel.set_tenant(id)` at the beginning of a request and then have every
> query to a table with `tenant_id` automatically have a `where(tenant_id:
> id)` added. If I wanted to implement that sort of solution, what level of
> the Sequel stack would I want to hook into? Would the `dataset` method
> associated with each `Model` be the best candidate?
>
A possible approach would be:
class << Sequel
attr_accessor :tenant_id
end
DB.extend_datasets do
def from(*a)
ds = super
if a.length == 1 && ds.columns.include?(:tenant_id)
table = a.first
table = table.alias if table.is_a?(Sequel::SQL::AliasedExpression)
key = Sequel.qualify(table, :tenant_id)
cond = Sequel.delay do
if tid = Sequel.tenant_id
{key=>tid}
else
true
end
end
ds = ds.where(cond)
end
ds
end
end
I'm not sure that will handle all cases correctly, but it should be a
start. Alternatively, you could hook into Dataset#select_where_sql, or
just manually call Dataset#where with the condition given in that code
example on the datasets where you want the behavior. The trick here is
using Sequel.delay to setup a delayed evaluation, so that it checks
Sequel.tenant_id at runtime and uses it if available.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
