Understanding Extracting After Combining Rails 3 Tables

I'm a beginner at Rails and SQL, so I'm sorry if there are any expressions that are difficult to understand.

First, we have the following models:

class City<ActiveRecord::Base
    has_many —favorites
    has_many:users,through::favorites

    has_many —areas
    has_many:countries, through::areas
end


class Favorite <ActiveRecord::Base
  belongs_to —user
  belongs_to —city
end

class User<ActiveRecord::Base
    has_many —favorites
    has_many:cities, through: —favorites
end


class Area<ActiveRecord::Base
  belongs_to —city
  belongs_to —Country
end

class Country <ActiveRecord::Base
    has_many —areas
    has_many:cities, through: —areas
end

The Favorite model is an intermediate table with the User model centered on the City model.The Area model is an intermediate table with the Country model.

I would like to extract the matches between the City model and the Country model, and sort the extracted matches in order of the number of City models registered in the Favorite model.
For example, if you want to sort the extracted items in the Country model America, I wrote the following, but it doesn't work.

@city=City.includes(:countries).joins(:favorites).group(:city_id).where("name=?", "America").order('count(city_id)desc')

How can I do this?

ruby-on-rails ruby rails-activerecord

2022-09-30 14:09

1 Answers

Perhaps this is how it works.

distinct_cities=
  City.joins(:countries).select(:id)
    .where (countries: {name: 'America'})
    .group(:id)    
City
  .joins("INNER JOIN(#{distinct_cities.to_sql}) ASt ON cities.id=t.id")
  .left_joins(:favorites)
  .group('cities.id')
  .select('cities.*', 'count(favorites.id)AS favs')
  .order('favs desc')

2022-09-30 14:09

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656