Using Rails 6, I'm trying to set a has_many association with a join to a postgres jsonb column.
class Form
has_many :automation_triggers, foreign_key: "preferences->>'form_id'"
end
This results in the error:
ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR: operator does not exist: bigint = jsonb)
The foreign key needs be cast as an integer to resolve the error so the query should look like:
SELECT "automation_triggers".* FROM "automation_triggers" INNER JOIN forms ON (forms.id = (automation_triggers.preferences->'form_id')::int)
Is there a way to apply a cast to a has_many association foreign key? or write a custom join query for the association?