CODEWITHSUNDEEP
ruby-on-railsactiverecord
AlbertMunichMar
AlbertMunichMar

Reputation: 1856

Activerecord query: sort model by average of association's model

I have a model Place which has reviews, which has a column stars.

I tried following query:

Place.select('place_id, name, avg(reviews.stars)').join(:reviews).group('place_id, name').order('avg(reviews.stars) desc')

I got following error:

PG::UndefinedColumn: ERROR: column "place_id" does not exist LINE 1: SELECT place_id, name, avg(reviews.stars) FROM "places" ^ : SELECT place_id, name, avg(reviews.stars) FROM "places"

How can he complain about place_id? This column is created by Rails. How can I solve this issue?

My models are:

class Review < ApplicationRecord
  belongs_to :place
end

and

class Place < ApplicationRecord
  has_many :reviews
end

The schema is as follows:

  create_table "reviews", force: :cascade do |t|
    t.integer "stars"
    t.string "content"
    t.bigint "place_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["place_id"], name: "index_reviews_on_place_id"
  end

and

  create_table "places", force: :cascade do |t|
    t.bigint "user_id"
    t.string "name"
    t.string "description"
    t.float "lng"
    t.float "lat"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.float "latitude"
    t.float "longitude"
    t.string "address"
    t.index ["user_id"], name: "index_places_on_user_id"
  end

Upvotes: 0

Views: 32

Answers (1)

Marcin Kołodziej
Marcin Kołodziej

Reputation: 5313

join is a method that is being delegated to underlying records, resulting in Array#join being called directly after your select.

You want to call joins instead of join to construct a proper query. If you take a closer look at your error, you will see that everything after select has been ignored.

Upvotes: 1

Related Questions