Make has_and_belongs_to_many relationships work when using Citus (include tenant_id)

[lfittl]

Currently a query that uses has_and_belongs_to_many (habtm) will not pull in the tenant_id for the habtm table itself.

This happens even if the table includes the tenant_id as an additional column, due to the Rails/ActiveRecord query builder not knowing that it needs to be included at all times.

We should be able to patch up ActiveRecord to include the tenant_id correctly:

• Variant 1) Include an explicit habtm_table.tenant_id = $1 condition

• Variant 2) Include the tenant_id in the JOIN between the habtm_table and the target_table

Until this is fixed this can be worked around by creating an explicit model for the intermediate table.

[fastjames]

Is the only requirement that the model exist and point to the habtm table (and that the table have a tenant id column)? You don't have to promote it to a has_many :through relationship?

[lfittl]

@fastjames If I'm remembering correctly, you will need to make an explicit model and then use has_many :through with the model, instead of has_and_belongs_to_many.

Typically that is a good change to make anyway, since habtm relations are a bit magic with Rails. That said, I'm still looking to fix this at some point, and PRs are of course welcome :)

[fastjames]

I dug into acts_as_tenant a little and found this very old PR: https://github.com/ErwinM/acts_as_tenant/pull/16

I assume that the tenant_id is useful here because you would want to shard the table in citus? I'm not sure what else it would be used for.

[lfittl]

@fastjames Yeah - this issue specifically refers to making it compatible with Citus and being able to distribute the habtm linking table (i.e. including the tenant_id always, and adding it on the table as well).

Outside Citus you should be fine to use has_and_belongs_to_many. I'll update the issue title to reflect this :)

[h0jeZvgoxFepBQ2C]

I just tried to make some hbtm relations, and it seems that even without citus the tenant_id is not set - even when the column exists?

Should this work?

[Laykou]

Is the HABTM supported now? If we add tenant_id to the many-to-many join table, will it automatically also shared correctly with the rest of the data?

Is the tenant_id column handled automatically now for HABTM?

[heldopslippers]

is still an issue? Anybody has a workaround for this?

[gurkanindibay]

@fastjames @lfittl @h0jeZvgoxFepBQ2C @Laykou Solved the issue Please check out https://github.com/citusdata/activerecord-multi-tenant/pull/193