Many to many relationship with Ruby on Rails

Question

I'm working on my first project with RoR and I need to create many to many relationship between two models but with possibility of ordering objects of first model in association to second model.

Let's say that I have two following models - Customer - Route

I want assign many Customers to many Routes but with storing order of this association, so for example

-Route 1 --Customer 2, position 1 --Customer 1, position 2

-Route 2 --Customer 3, position 1 --Customer 1, position 2

I think I have to use for it has_many :through and belong_to and creat "position" field in in-middle-table but then how to make this field accessible and editable?

Answer 1

You could go with :through:

class Route < ActiveRecord::Base
  has_many :bookings
  has_many :routes, :through => bookings
end

class Booking < ActiveRecord::Base
  belongs_to :route
  belongs_to :customer
end

class Customer < ActiveRecord::Base
  has_many :bookings
  has_many :routes, through => :bookings
end

Your Bookings model would keep the date/position and you could access them with:

c = Customer.first
c.routes(:include => :bookings, :order => "bookings.position")

Answer 2

class Customer < ActiveRecord::Base
  has_many :routes, :through => :trips
  ...
end

class Trip < ActiveRecord::Base
  belongs_to :customer
  belongs_to :route
  // has a ordinal called 'seq'
  ...
end

class Route < ActiveRecord::Base
  has_many :customers, :through => :trips
  ...
end

You should be able to access the ordinal field via @customer.routes[0].seq I haven't tested it and my rails skills are long dull but you should get the idea.

Answer 3

cite's solution is right. to access the middle table, try:

c.bookings # this will return the 'middle table' which then you have the access to the positions 
c.bookings.first.position

Answer 4

I want to display all customers assignes to one routes, so the query sent to database are:

SELECT customers.* FROM customers INNER JOIN bookings ON customers.id = bookings.customer_id WHERE ((bookings.route_id = 1))

and then

SELECT bookings.* FROM bookings WHERE (bookings.customer_id IN (1,2,3))

..so, the 2nd query does not contain enought information in WHERE calus, because it's selecting information for all routes for specific customers, not just about specific customers for specific route (id=1).

..is RoR doing some extra filtering after fetching this data?

btw. I have :include => :bookings added into Route model