Rails Bridges里討論版的練習(xí)題里面，有個進(jìn)階的作業(yè)，找了兩天才找到解法。
題目是：討論版的目錄里可見標(biāo)題，加、減投票按鈕和投票數(shù)量，默認(rèn)是按照標(biāo)題的創(chuàng)建時(shí)間排序的，要求按照票數(shù)排序。
因?yàn)槠睌?shù)的記錄是在associations的Vote model里的，正常查詢只能在TopicActionController里面查到Topics Model數(shù)據(jù)表的所有數(shù)據(jù)。
其中：
在model里的定義
? class Topic < ApplicationRecord
? ? has_many :votes, dependent: :destroy
? end
? class Vote < ApplicationRecord
? ? belongs_to :topic
? end
解法：在controller里面更新的代碼：
class TopicsController < ApplicationController
? before_action :set_topic, only: [:show, :edit, :update, :destroy]
? # GET /topics
? # GET /topics.json
? def index
? ? @topics = Topic.joins(:votes).group("topics.id").order("count(topics.id) DESC")
? end\
如果在topics的頁面按照votes的數(shù)量排序，則需要用**joins**方法使得votes.topic_id = topic.id, 并用**group**方法得到一個基于topic.id的collection object，并根據(jù)topics.id的計(jì)數(shù)來排序。
Active Record Query Interface — Ruby on Rails Guides: joins
Active Record Query Interface - Ruby on Rails Guides: group
For example, consider the following Category, Article, Comment, Guest and Tag models:
class Category < ApplicationRecord
? has_many :articles
end
class Article < ApplicationRecord
? belongs_to :category
? has_many :comments
? has_many :tags
end
12.1.2.1 Joining a Single Association
Category.joins(:articles)
This produces:
SELECT categories.* FROM categories
? INNER JOIN articles ON articles.category_id = categories.id
Or, in English: "return a Category object for all categories with articles". Note that you will see duplicate categories if more than one article has the same category. If you want unique categories, you can use Category.joins(:articles).distinct.
Finder methods that return a collection, such as?where?and?group, return an instance of?ActiveRecord::Relation.?
6 Group
To apply a?GROUP BY?clause to the SQL fired by the finder, you can use the?group?method.
6.1 Total of grouped items
To get the total of grouped items on a single query, call?count?after the?group.
Rails服務(wù)器的 log:
Started GET "/topics" for 127.0.0.1 at 2018-03-18 11:40:13 +0800
Processing by TopicsController#index as HTML
? Rendering topics/index.html.erb within layouts/application
? Topic Load (0.5ms)? SELECT "topics".* FROM "topics" INNER JOIN "votes" ON "votes"."topic_id" = "topics"."id" GROUP BY topics.id ORDER BY count(topics.id) DESC
? (0.4ms)? SELECT COUNT(*) FROM "votes" WHERE "votes"."topic_id" = ?? [["topic_id", 2]]
? (0.2ms)? SELECT COUNT(*) FROM "votes" WHERE "votes"."topic_id" = ?? [["topic_id", 1]]
? (0.2ms)? SELECT COUNT(*) FROM "votes" WHERE "votes"."topic_id" = ?? [["topic_id", 3]]
? Rendered topics/index.html.erb within layouts/application (11.8ms)
Completed 200 OK in 46ms (Views: 40.5ms | ActiveRecord: 1.3ms)
