CODEWITHSUNDEEP
mysqlruby-on-railspostgresqlactiverecord
user2942937
user2942937

Reputation: 3

Sort records according to value in associated model

I have two models: School and Address.

Address
    has_many :schools

School
    belongs_to :address

    t.bigint "address_id"

Now I need to build scope in School model to get Schools sorted according to occurence of cities in Address table. So I need following result:

School_1 | City_most_popular
School_2 | City_most_popular
School_3 | City_most_popular
School_4 | City_most_popular
School_5 | City_quite_popular
School_6 | City_quite_popular
School_7 | City_not_popular

I wrote this: School.joins(:address).group("schools.id, addresses.city").order("addresses.city ASC") but this only allows to group records by cities and ignore occurence.

Upvotes: 0

Views: 61

Answers (1)

mingca
mingca

Reputation: 662

School
 .joins(:address)
 .select('schools.*, count(*) OVER (PARTITION BY addresses.city) AS count')
 .order('count DESC')

Upvotes: 1

Related Questions