CODEWITHSUNDEEP
ruby-on-railsjoin
Mayur Patel
Mayur Patel

Reputation: 799

Multi level joins in rails 4

I want to make this query in rails 4 

select r.region_id, r.region_name from countries c, zones z, regions r where c.country_id = $country_id (pass as parameter) and c.country_id = z.zone_id and z.subzone_id = r.region_id

Models :

 #Country.rb
class Country < ActiveRecord::Base
  has_one :place, foreign_key: :place_id
  has_many :zones , foreign_key: :place_id
  has_many :subzones, :through => :zones
end

#Zone.rb
class Zone < ActiveRecord::Base
 belongs_to :place
 belongs_to :subzone, :class_name => 'Place' , :foreign_key => :subzone_id
end

#Region.rb
class Region < ActiveRecord::Base
  has_one :place , foreign_key: :place_id
end

#Place.rb
class Place < ActiveRecord::Base
  belongs_to :region, :foreign_key => :place_id
  has_many :zones, :foreign_key => :place_id
  has_many :subzones, :through => :zones
end

I have tried this:

Country.joins(:zones).where("zone.subzone_id = regions.region_id AND country_id = ?",$country_id )

but got error as :

Java::JavaSql::SQLSyntaxErrorException: ORA-00904: "REGIONS"."REGION_ID": invalid identifier.....

Not sure how to load region in above query...

Thanks in advance :-)

Upvotes: 3

Views: 4062

Answers (2)

John Hayes-Reed
John Hayes-Reed

Reputation: 1438

when using string for the where statement after a join or include, you need to use the table name, not the relation name to refer to, so where you have written 

zone.subzone_id

you would need to write

zones.subzone_id

Your models and relations are a bit confusing and its hard to see what your trying to achieve relation wise with them, place_id seems to be being used as a reference for many things that have nothing to do with the Place class, and it could get a bit messy in the future. However from what I can grasp, in terms of multi level joins, something like:

Country.joins(zones: {place: :region}).where.........

is something along the lines of what you want to set up your table joins for your query. Play around with that or things similar, hope it helps.

Upvotes: 0

khaled_gomaa
khaled_gomaa

Reputation: 3422

Region.joins(zones: :country).where(country: {country_id: $country_id})

This will only work if you have your models like this:

#Country.rb
class Country < ActiveRecord::Base
  has_many :zones, as: :zone
end
#Zone.rb
class Zone < ActiveRecord::Base
  has_many :regions, as: :region
  belongs_to :country, foreign_key: :zone_id
end

#Region.rb
class Region < ActiveRecord::Base
  belongs_to :zone, foreign_key: :region_id
end

Upvotes: 7

Related Questions