Handling Conditional `belongs_to` Associations with Different Foreign Key Types in Rails

I'm working on a Rails application where I need to manage a polymorphic association with different types of foreign keys based on the reporter_type attribute. The main challenge is that the foreign key types differ (integer for reporter_id and string for partition_reporter_id), and I need to conditionally switch between these keys within the same model.

I am using rails 7.0.8.4

Current Setup In my Report model, I have a polymorphic association defined as follows:

class Report < ApplicationRecord

  belongs_to :report_configuration, counter_cache: true
  belongs_to :reporter, polymorphic: true, optional: true

  Other associations and validations...
end

The reporter_id typically maps to customer_id (integer), but I need to introduce partition_reporter_id to handle a string UUID for Salesforce IDs.

Requirements If reporter_type is 'partition', the foreign key should be partition_reporter_id. For all other values of reporter_type, the foreign key should be reporter_id.

Challenges Rails' standard polymorphic associations expect the same data type for the foreign key across different types of associated records. Using two different data types complicates the model's logic and the SQL queries Rails builds. Rails does not natively support conditional logic directly in association definitions.

Questions How can I modify or extend the belongs_to :reporter association to conditionally use different foreign keys (partition_reporter_id or reporter_id) based on the reporter_type? Are there any best practices or design patterns in Rails to handle such a scenario without compromising maintainability and performance? Any insights or suggestions on how to approach this problem would be greatly appreciated!

It's not possible and for pretty good reason.

In ActiveRecord, all associations (even polymorphic ones) have a fixed foreign key column.

When you join through a assocation Rails creates the following SQL

SELECT "reports".* FROM "reports" 
INNER JOIN "reporters" 
ON "reports"."id" = "reporter"."report_id"

If you wanted to perform a join where the foreign column is dynamic it could perhaps be done through a case statement:

SELECT "reports".* FROM "reports" 
INNER JOIN "reporters"
  ON CASE
  WHEN reports.reporter_type = 'partition' THEN
    reports.partition_reporter_id = reporters.some_column
  ELSE 
    reports.id = reporters.rapporter_id

That doesn't seem so bad until you remember that ActiveRecord is supposed to be polyglot and support a wide array of relational database management systems and that Rails has many methods for joining and eager loading. If you then sprinkle some polymorphism on top of this it gets even worse.

This little feature would add tons of complexity for very little actual gain because what you're looking to do is fighting the very nature of how a relational database is meant to work.

A better solution is to just rethink it and consider having both a surrogate key (an autoincrementing integer) and the external identifier (the salesforce uuid) or even an intermediate model which stores the external identifier.