ActiveRecord query for two belongs_to on the same model?

Question

I have the following two models:

class Travel < ApplicationRecord
  belongs_to :arrival_competition_participation,
             class_name: 'CompetitionParticipation',
             foreign_key: 'arrival_competition_participation_id',
             inverse_of: :arrival_travels,
             optional: true
  belongs_to :departure_competition_participation,
             class_name: 'CompetitionParticipation',
             foreign_key: 'departure_competition_participation_id',
             inverse_of: :departure_travels,
             optional: true
[...]
end

and

class CompetitionParticipation < ApplicationRecord
  belongs_to :athlete
  has_many :arrival_travels,
           class_name: 'Travel',
           foreign_key: 'arrival_competition_participation_id',
           dependent: :destroy,
           inverse_of: :arrival_competition_participation
  has_many :departure_travels,
           class_name: 'Travel',
           foreign_key: 'departure_competition_participation_id',
           dependent: :destroy,
           inverse_of: :departure_competition_participation
[...]
end

How could I find all Travels that belong_to a certain Athlete (either through an arrival_competition_participation or through a departure_competition_participation?

I tried all kinds of different joins without success ... I'm at a point where I would even write raw SQL if it helps ...

Answer 1

try:

athlete_id = 123 # Replace with the ID of the athlete you're interested in

travels = Travel.joins(
  "LEFT JOIN competition_participations AS arrival_participations ON travels.arrival_competition_participation_id = arrival_participations.id",
  "LEFT JOIN competition_participations AS departure_participations ON travels.departure_competition_participation_id = departure_participations.id"
).where(
  "arrival_participations.athlete_id = :athlete_id OR departure_participations.athlete_id = :athlete_id",
  athlete_id: athlete_id
).distinct