Self join query in Rails. Undefined method error

Question

I am creating a bus timetable app in Rails 4 and I have a timetable Class on which each record has a bus stop and belongs to a bus service foreign key.

I want to display the origin and destination stops served by each bus service. I do a self join on the Timetable class (bus_service) to link the timetable entries for origin and destination stops belonging both to a specific bus service.

Model. timetable.rb

has_many :timetable_sisters, class_name: "Timetable", primary_key: "bus_service_id", foreign_key: "bus_service_id" 
belongs_to :timetable_sister_b, class_name: "Timetable", foreign_key: "bus_service_id"
belongs_to :bus_stop
belongs_to :bus_service

def self.joined(from_stop_id, to_stop_id)
joins(:timetable_sisters).
where("timetables.bus_stop_id = ? AND timetable_sisters_timetables.bus_stop_id = ?", from_stop_id, to_stop_id)
end

Controller:timetable_controller.rb

@timetables = Timetable.joined(params[:from_stop_id], params[:to_stop_id])

View: index.html.erb

<% @timetables.each do |timetable| %>
<tr>
<td><%= timetable.bus_service_id %></td>
<td><%= timetable.bus_stop.name %></td>
<td><%= timetable.timetable_sister.bus_stop.name %></td>
<td><%= timetable.departure_time %></td>
</td></tr>
<% end %>

The generated SQL looks fine:

SELECT "timetables".* 
FROM "timetables" 
INNER JOIN "timetable_sisters" "timetable_sisters_timetables" 
ON "timetable_sisters_timetables"."bus_service_id" = "timetables"."bus_service_id" 
WHERE (timetables.bus_stop_id = '25' 
AND timetable_sisters_timetables.bus_stop_id = '27')

But I get a undefined method `timetable_sister' error on the view file. NoMethodError in Timetables#index

How should I call for the sister station name in the view? Or I am doing something else wrong.

Thanks

you have a 'to many' relationship for timetable_sisters. Therefore you can't call .timetable_sister. Instead, you will need to iterate through the results, for example, timetable.timetable_sisters.each — Dave Satch
– Dave Satch, Commented Oct 20, 2015 at 5:14
That's great davidsatch. It works. :) but logically it gives the two sister timetable stops as result. How to limit the result to the one not listed on the column before? — Enrique
– Enrique, Commented Oct 21, 2015 at 15:41

Dave Satch · Accepted Answer · 2015-10-20 05:16:44Z

1

<% @timetables.each do |timetable| %>
<tr>
<td><%= timetable.bus_service_id %></td>
<td><%= timetable.bus_stop.name %></td>
<td><% timetable.timetable_sisters.each do |t_sis| %>
    <%= t_sis.bus_stop.name %>, 
<% end %></td>
<td><%= timetable.departure_time %></td>
</td></tr>
<% end %>

answered Oct 20, 2015 at 5:16

Dave Satch

1,1617 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Enrique Over a year ago

That's great davidsatch. It works. :) but logically it gives the two sister timetable stops as result. How to limit the result to the one not listed on the column before?

Dave Satch Over a year ago

you will need to use the bus service to identify each route and only show them once. For example, instead of listing though timetables, you may want to go through each bus_service and display each of the stops in that service.

Richard Peck · Accepted Answer · 2015-10-20 16:49:52Z

For the sake of my sanity,

#app/models/timetable.rb
class Timetable < ActiveRecord::Base
   #columns id | service_id | bus_stop_id | arrival_time | created_at | updated_at
   belongs_to :bus_stop
   belongs_to :service
end

#app/models/service.rb
class Service < ActiveRecord::Base
   #columns id | name | number | etc | created_at | updated_at
   has_many :timetables
   has_many :bus_stops, through: :timetables
end

#app/models/bus_stop.rb
class BusStop < ActiveRecord::Base
   #column id | name | geox | geoy | created_at | updated_at
   has_many :timetables
   has_many :services, through: :timetables
end

I want to display the origin and destination stops served by each bus service

Then you'll be able to do:

@services = Service.all
@services.each do |service|
   # service.number -> "56"
   service.bus_stops.each do |stop|
      stop.name #-> "
   end
end

If you wanted to plot a specific route, you'd be able to do the following:

@service = Service.find "1"
@services = Timetable.where service: @service

@services.each do |route|
   route.bus_stops.each do |stop|
      stop.name
      stop.geox
      stop.geoy
   end
end

This is still sketchy, but it should work.

I have not included the "destination" / "origin" bus stops because they aren't defined in the models. I want to include a Route model but I just couldn't make it work from the example data.

Hope this helps!

Collectives™ on Stack Overflow

Self join query in Rails. Undefined method error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related