如何在SQL或作用域中按列数对资源进行排序

Question

我有两个模型，photos and albums。

照片has many相册。

相册has many照片。

我有一个名为Photo Listings的联接表。

我想把我的照片按大多数相册排序，但我无法做到这一点！

@most_albums_photos = @photos.joins(:photo_listings).order('COUNT(albums.id)')

我得到以下错误：

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "albums"
LINE 1: SELECT  DISTINCT "photos".id, COUNT(albums.id) AS alias_0 FR...

家里有SQL人吗？感谢

架构

  create_table "photo_listings", force: true do |t|
    t.integer  "album_id"
    t.integer  "photo_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end
  create_table "albums", force: true do |t|
    t.string   "title"
    t.integer  "user_id"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.text     "description"
    t.integer  "cached_votes_total",      default: 0
    t.integer  "cached_votes_score",      default: 0
    t.integer  "cached_votes_up",         default: 0
    t.integer  "cached_votes_down",       default: 0
    t.integer  "cached_weighted_score",   default: 0
    t.integer  "cached_weighted_total",   default: 0
    t.float    "cached_weighted_average", default: 0.0
    t.boolean  "selected",                default: false
  end
  create_table "photos", force: true do |t|
    t.string   "title"
    t.text     "description"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.string   "photo"
    t.integer  "user_id"
    t.integer  "category_id"
    t.integer  "zone_id"
    t.integer  "pins_count",            default: 0
    t.integer  "cached_votes_total",    default: 0
    t.integer  "cached_votes_score",    default: 0
    t.integer  "cached_votes_up",       default: 0
    t.integer  "cached_votes_down",     default: 0
    t.integer  "cached_weighted_score", default: 0
    t.boolean  "terms"
    t.string   "slug"
    t.boolean  "sponsored"
    t.integer  "order"
    t.string   "url"
    t.boolean  "editor_pick",           default: false
    t.tsvector "tsv"
    t.boolean  "flash"
    t.boolean  "flash_active"
  end

Answer 1

@Yoshji先生几乎到了。最后，我使用

@most_albums_photos = @photos.joins(photo_listings: :album).group("photos.id").order('COUNT(albums.id) DESC')