Nested Rails Joins within Scopes

Question

I've got 3 classes. Country, City, and Activity. A country has_many cities and a city has_many activities.

Some of the activities aren't ready yet, so the activities have an approval status. I'd like to set it up scopes, so a city without approved activities isn't valid and a country without valid cities is also invalid.

class ApprovalStatus < ActiveRecord::Base
  def self.approved_status_id
    ApprovalStatus.find_or_create_by(name: 'Approved').id
  end 

class Activity < ActiveRecord::Base
  # Named scopes
  scope :that_is_approved, -> { where approval_status_id: ApprovalStatus.approved_status_id }

class City < ActiveRecord::Base
  # Named scopes
  scope :that_has_valid_activities, -> { joins(:activities).merge(Activity.that_is_approved).uniq }

class Country < ActiveRecord::Base
  # Named scopes
  scope :that_has_valid_cities, -> { joins(:cities).uniq }

  # Associations
  has_many :cities, -> { that_has_valid_activities }, dependent: :destroy

I want the scope and the association as I want Country.that_has_valid_cities to only return the valid countries and only the valid cities to be shown within those valid countries when the active model serializer grabs the cities.

In the rails console: City.that_has_valid _activities runs fine.

ApprovalStatus Load (13.4ms)  SELECT  "approval_statuses".* FROM "approval_statuses" WHERE "approval_statuses"."name" = $1 LIMIT 1  [["name", "Approved"]]
City Load (21.6ms) SELECT DISTINCT "cities".* FROM "cities" INNER JOIN "activities" ON "activities"."city_id" = "cities"."id" WHERE "activities"."approval_status_id" = $1  [["approval_status_id", 2]]

Country.that_has_valid_cities breaks.

ApprovalStatus Load (0.4ms)  SELECT  "approval_statuses".* FROM "approval_statuses" WHERE "approval_statuses"."name" = $1 LIMIT 1  [["name", "Approved"]]
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "activities" LINE 1: ..." ON "cities"."country_id" = "countries"."id" AND "activitie...
                                                         ^
: SELECT DISTINCT "countries".* FROM "countries" INNER JOIN "cities" ON "cities"."country_id" = "countries"."id" AND "activities"."approval_status_id" = $1

It looks like it's trying to do an AND when I want it to do a second INNER JOIN.

class Country < ActiveRecord::Base

  # Named scopes
  scope :that_has_valid_cities, -> { joins(:cities).merge(City.that_has_valid_activities).uniq }

  # Associations
  has_many :cities, dependent: :destroy

Works and produces the queries:

  ApprovalStatus Load (0.6ms)  SELECT  "approval_statuses".* FROM "approval_statuses" WHERE "approval_statuses"."name" = $1 LIMIT 1  [["name", "Approved"]]
  Country Load (1.2ms)  SELECT  DISTINCT "countries".* FROM "countries" INNER JOIN "cities" ON "cities"."country_id" = "countries"."id" LEFT OUTER JOIN "activities" ON "activities"."city_id" = "cities"."id" WHERE "activities"."approval_status_id" = $1  ORDER BY "countries"."id" ASC LIMIT 1  [["approval_status_id", 2]]

However, the countries returned still list their invalid cities as the invalid cities aren't filtered from within the valid countries.

P.S. Let me know if the entire style is off here as I'm learning rails on my own.

Answer 1

I think you could check for the reverse. Check if there's any activity, city or country that's invalid. You just need to run a find_by. If it returns nilthen you're golden, if it returns an object then it's invalid.

Good RoRing

Answer 2

# app/models/approval_status.rb
class ApprovalStatus < ActiveRecord::Base
  has_many :activities

  def self.approved_status
    # I added some caching
    return @approved_status if @approved_status
    @approved_status = ApprovalStatus.find_or_create_by(name: 'Approved')
  end
end

# app/models/activity.rb
class Activity < ActiveRecord::Base
  belongs_to :city
  belongs_to :approval_status

  scope :that_is_approved, -> { where approval_status: ApprovalStatus.approved_status }
end

# app/models/city.rb
class City < ActiveRecord::Base
  belongs_to :country
  has_many :activities

  scope :that_has_valid_activities, -> { joins(:activities).where(activities: {id: Activity.that_is_approved}) }
end

# app/models/country.rb
class Country < ActiveRecord::Base
  has_many :cities, -> { that_has_valid_activities }, dependent: :destroy
  # I added this (probably would help you along the way in case you do not know yet about :through)
  has_many :activities, through: :cities

  scope :that_has_cities_having_valid_activities, -> { joins(cities: :activities) }
end

Usage

Country.that_has_cities_having_valid_activities
City.that_has_valid_activities

Gotcha

• Take note that

  Country.joins(:cities) will no longer work, and will generate and Error. You'll need it always joined with :activities like so: Country.joins(cities: :activities)

  • This is because your has_many :cities, -> { that_has_valid_activities } is querying upon the associated :activities. Couldn't find a way to automagically make this work.

Recommendation

• I advise not using

  has_many :cities, -> { that_has_valid_activities }
  scope :that_has_cities_having_valid_activities, -> { joins(cities: :activities) }
  

  but instead use:

  has_many :cities
  scope :that_has_cities_having_valid_activities, -> { joins(:cities).where(cities: {id: City.that_has_valid_activities} ) }
  

  • so that you can still query upon the "non-valid" cities. Otherwise, @country.cities will always be filtered with "valid" cities, and a possible future scenario of querying upon the "non-valid" ones will be tricky, and might involve some ugly code.

  • This will also fix the gotcha above

Tested working