Associations custom join on 2 columns

rubyonrails-talk
1

Summary

I would like to change the mapping between my models so that, in addition to the model IDs, another column is used too in the joins.

Current models and DB

I have the models models Account, Candidate and Activity and their corresponding tables. 1 account has many candidates, 1 candidate has many activities. We are using Postgres.

CREATE TABLE public.accounts (
    id bigint NOT NULL,
    name character varying(255),
    ...
);


CREATE TABLE public.candidates (
    id bigint NOT NULL,
    account_id bigint,
    name character varying(255),
    ...
);

CREATE TABLE public.activities (
    id bigint NOT NULL,
    candidate_id bigint,
    account_id bigint,
    ...
);

And the corresponding models:

# account.rb
class Account < ApplicationRecord
   has_many :candidates

# candidate.rb
class Candidate < ApplicationRecord
    belongs_to :account
    has_many :activities

# activity.rb
class Activity < ApplicationRecord
    belongs_to :candidate

Required change

For performance reasons, I am assessing partitioning the tables candidates and activities by account_id. For Postgres declarative partitioning, the partitioned tables will have composite PKs containing the partitioning key too, so the new PKs of the partitioned tables will be (account_id,id).

In order for partitioning to give us performance, the mapping between the model needs to change to use the account id too when we join the activities table. This will ensure that the query planner knows which partition to search the data in.

For example, with our current schema, when we do

candidate.activities

the generated query looks like:

SELECT * from activities where candidate_id = ?

When we switch to the partitioned tables, the query should look like:

SELECT * from activities where candidate_id = ? and account_id = ?

Rails support for composite PK support

I first tried to achieve the desired query with with Rails 7.1, which brought support for composite PKs. I also tried the composite_primary_keys gem. The problems I have with both these is that:

  • They are meant for “actual” composite keys, meaning cases where you indeed need multiple columns to identify a model. For this reason, they break the route URLs to include all composite PK columns. For example, /activities/123 becomes /activities/123_456 (where 456 is a hypothetical account_id). This will break all my clients.
  • They need too much change in the code, fixtures, etc

Scope using the instance on the association

I managed to get the desired query by introducing a scope on the associations like so:

# account.rb
class Account < ApplicationRecord
   has_many :candidates

# candidate.rb
class Candidate < ApplicationRecord
    belongs_to :account
    has_many :activities, ->(candidate) { where("activities.account_id = ?", candidate.account_id) }

# activity.rb
class Activity < ApplicationRecord
    belongs_to :candidate, ->(activity) { where("candidates.account_id = ?", activity.account_id) }

The problem with this, as described in the linked documentation, is that it breaks eager loading of the associations. Eager loading is not supported for instance dependent associations.

Question

Is there an elegant way to change the mapping between the models so that account_id is also introduced in the queries between the models?

1 Like