ruby on rails - ActiveRecord and and SQL AS -

ruby on rails - ActiveRecord and and SQL AS -

i trying self bring together on models table , table like

the sql should that

select models.id models, shows, models others models.location = shows.first_location , others.location = shows.second_location;

location, first_location, second_locations integers

i couldn't find how can refer models , models others in same query

i tried like

model.joins("left outer bring together shows on models.location = shows.first_location").joins("models others").where("others.location = shows.second_location").where("models.id = 12")

this doesn't work, hoping give me models in sec location of show model.id = 12 in first location

you should rails association options rather hand-coded sql.

i think schema needs rethought: taking step back, , looking @ in natural language, have situation each location has shows , models. each show has first location , sec location. show more have list of locations, each of has field allowing them ordered?

it's not clear requirements should able this. haven't tested btw think should work.

class model belongs_to :location def location_shows self.location && self.location.shows end class location has_many :models has_many :shows class show has_many :show_locations, :order => :position has_many :locations, :through => :show_locations #you can have these convenience methods if want might not need them. def first_location self.locations[0] end def second_location self.locations[1] end class showlocation belongs_to :show belongs_to :location

now, associations simple rails assocations no complicated sql. let's see if can maintain way.

we want method model, gets associated shows , returns models in other locations listed show.

#in model def models_in_shows_other_locations self.location_shows.select{|show| show.locations.select{|l| l.id != self.location_id}.collect(&:models) end

sql ruby-on-rails activerecord