How to access model from two distinct foreign keys?

Question

I have two related models, Trip and Ride. A Trip may have a going ride or a returning ride. My ActiveRecord models look like this:

class Route < ActiveRecord::Base
    has_one :trip
end

class Trip < ActiveRecord::Base
    belongs_to :going_route,     class_name: "Route"
    belongs_to :returning_route, class_name: "Route"
end

However this throws me an issue when I want to access a trip from a route:

Route.first.trip

This throws a PostgreSQL error:

PG::UndefinedColumn: ERROR: column trips.route_id does not exist

How can I tell to the Route class that his trip is under either going_route_id or returning_route_id? Or maybe there is another way around?

P.S: I've been looking for similar questions, there are a lot, but none is exactly like this one and solves my problem. If you have any tip on how to make the difference clearer, especially for the title. Here is some similar question

---

EDIT:

I've also tried using a lambda, as in matthew's duplicate proposition:

class FavoriteRoute < ActiveRecord::Base
    has_one :favorite_trip, -> (route) { where("going_route_id = :id OR returning_route_id = :id", id: route.id) }
end

This will throw the same error. And if I assume I should use find_by instead of where since I need only one result, I have another error which I really don't understand:

NoMethodError: undefined method `except' for #<Trip:0x00007f827b9d13e0>

Answer 1

You need to specify the foreign keys on the inverse side of the belongs_to association - meaning the has_one / has_many side that references the foreign key:

class Trip < ActiveRecord::Base
    # specifying foreign_key here is not needed since AR
    # will deduce that its outbound_route_id
    belongs_to :outbound_route,
      class_name: "Route"
    belongs_to :return_route, 
      class_name: "Route"
end

class Route < ActiveRecord::Base
  has_one :trip_as_outbound,
    class_name: 'Trip',
    foreign_key: :outbound_route_id
  has_one :trip_as_returning,
    class_name: 'Trip',
    foreign_key: :return_route_id

  def trip
    trip_as_outbound || trip_as_returning
  end
end

One way around this is to use Single Table Inheritance:

class Route < ApplicationRecord 
end

class Routes::Outbound < ::Route
  self.table_name = 'routes'
  has_one :trip, foreign_key: :outbound_route_id
end

class Routes::Return < ::Route
  self.table_name = 'routes'
  has_one :trip, foreign_key: :return_route_id
end

class Trip < ApplicationRecord
  belongs_to :outbound_route,
    class_name: '::Routes::Outbound'
  belongs_to :return_route,
    class_name: '::Routes::Return'
end

Which will give you the correct trip but has some strangeness such as that Routes::Return.all will give you the same result as Route.all.

This can be fixed by adding a type string column to the routes table. For performance add a compound index on type and id.

Answer 2

Being that there is no functional difference between a going and a returning route, consider setting up a has_many relationship between the trip and the route. This would make routes reusable for other trips, and get you what you're looking for.

NOTE: There are flaws in this approach, because you're using a many-to-many relationship. Which means one trip could have more than one going and/or returning route. You could manage this through code in the Trip model, or this may not be so bad, if you wanted to generate a "multi-stop" route for either direction.

You would generate a model called called trip_routes.

The trip_routes migration might look like this:

create_table :trip_routes do |t|
   t.integer :trip_id
   t.integer :route_id
   t.string  :route_type
   t.boolean :favorite
end

# Consider this part based on how you think your indexes are best built, I'm 
# just making note that DB performance can be impacted particularly on these
# two fields.
add_index :trip_routes, :trip_id
add_index :trip_routes, :route_id

Your trip_route model would look like this:

class TripRoute < ActiveRecord::Base
    belongs_to :trip
    belongs_to :route

    # This model knows whether it's the 'going' or 'returning' route, so do your 
    # route functionality here.
end

Then your trip model would look like this:

class Trip < ActiveRecord::Base
    has_many :trip_routes
    has_many :route, through: trip_routes

    # Helper to get the going route
    def going_trip_route
        self.trip_routes.find_by(route_type: "going")
    end

    # Helper to get the going route
    def returning_trip_route
        self.trip_routes.find_by(route_type: "returning")
    end

end

Your route model would look like this:

class Route < ActiveRecord::Base
    has_many :trip_routes
    has_many :trips, through: trip_routes
end

Answer 3

After seeing every possible solution, and our needs, we chose this next solution:

class Route
    def trip
        @trip ||= Trip.find_by("going_route_id = :id OR returning_route_id = :id", id: id)
    end
end

I don't think this is the best way around, and it feels hacky. However, this was the fastest to implement, without perf issue. The other issue with this solution is that there is no rails validation.

Answer 4

try adding keys in routes table

add_column :routes, :going_key,     :integer
add_column :routes, :returning_key, :integer

then in your Trip and Route model

class Route < ActiveRecord::Base
  belongs_to :going_route, foreign_key: :going_key, class_name: Trip
  belongs_to :returning_route, foreign_key: :returning_key, class_name: Trip
end

class Trip < ActiveRecord::Base
end

Route.first.going_route 
Route.first.returning_route