Rails Bridges里讨论版的练习题里面,有个进阶的作业,找了两天才找到解法。
题目是:讨论版的目录里可见标题,加、减投票按钮和投票数量,默认是按照标题的创建时间排序的,要求按照票数排序。
因为票数的记录是在associations的Vote model里的,正常查询只能在TopicActionController里面查到Topics Model数据表的所有数据。
其中:
在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的数量排序,则需要用**joins**方法使得votes.topic_id = topic.id, 并用**group**方法得到一个基于topic.id的collection object,并根据topics.id的计数来排序。
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服务器的 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)
网友评论