Ruby China

新手问题 联表查询写法的疑问

wwwicbd · June 23, 2017 · Last by theblock24block replied at June 23, 2017 · 1772 hits

channel 有许多 post, post 属于某个 channel. 现在想查 post 数最多的 channel , sql 如下。

请问 如果不用 find_by_sql 要怎么写这个查询呢？谢谢啦

SELECT
    channels.*, count_t.p_count
FROM
    channels ,
    (
        SELECT
            channel_id ,
            count(posts.id) AS p_count
        FROM
            posts
        GROUP BY
            posts.channel_id
        ORDER BY
            p_count DESC
        LIMIT 10 OFFSET 0
    ) AS count_t
WHERE
    channels.id = count_t.channel_id
1 likes
hz_qiuyuanxin #1 June 23, 2017

我能想到的 3 种方案

  1. 像你上面这样
  2. 建立一个视图
  3. 缓存 channel 的 post 总数，存在 channel 表里的一个字段
1 likes
easonlovewan #2 June 23, 2017

加个计数器吧，省的麻烦

1 likes
3 Floor has deleted
will_c_j #4 June 23, 2017 
Channel.joins(:posts).select("channels.* , count(posts.channel_id) as p_count ").group("posts.channel_id").order("p_count desc")

p_count 可以。出来

wwwicbd #5 June 23, 2017

谢谢:)
像 p_count 这样临时加进来的属性在结果里怎么取得呢？我在 Model 里加 attr_accessor :p_count 也取不到

liuyang_1991 #6 June 23, 2017

counter_cache => true

2 likes
wwwicbd #7 June 23, 2017
Reply to will_c_j

我没有取到 p_count, 报 undefined method `p_count'

>> c.most_post.first
  Post Load (0.4ms)  SELECT  channel_id, count(id) as p_count FROM `posts` GROUP BY `posts`.`channel_id` ORDER BY p_count desc LIMIT 10
  Channel Load (0.6ms)  SELECT  `channels`.* FROM `channels` WHERE `channels`.`id` = 1 ORDER BY `channels`.`id` ASC LIMIT 1
  Channel Load (0.6ms)  SELECT  `channels`.* FROM `channels` WHERE `channels`.`id` = 2 ORDER BY `channels`.`id` ASC LIMIT 1
  Channel Load (0.4ms)  SELECT  `channels`.* FROM `channels` WHERE `channels`.`id` = 4 ORDER BY `channels`.`id` ASC LIMIT 1
=> [#<Channel id: 1, name: "Hello Focus", introduction: "这里是Focus的频道 😀", channel_type: "picture", intimity: "masses", user_id: 1, created_at: "2017-06-22 15:26:20", updated_at: "2017-06-22 15:26:20">, 5]
>> c.most_post.first.p_count
  Post Load (0.6ms)  SELECT  channel_id, count(id) as p_count FROM `posts` GROUP BY `posts`.`channel_id` ORDER BY p_count desc LIMIT 10
  Channel Load (0.5ms)  SELECT  `channels`.* FROM `channels` WHERE `channels`.`id` = 1 ORDER BY `channels`.`id` ASC LIMIT 1
  Channel Load (0.7ms)  SELECT  `channels`.* FROM `channels` WHERE `channels`.`id` = 2 ORDER BY `channels`.`id` ASC LIMIT 1
  Channel Load (0.3ms)  SELECT  `channels`.* FROM `channels` WHERE `channels`.`id` = 4 ORDER BY `channels`.`id` ASC LIMIT 1
NoMethodError: undefined method `p_count' for #<Array:0x007fb0b90da9a0>
will_c_j #8 June 23, 2017
Reply to wwwicbd

你用的是我第一个给你的查询吧，那个有 N+1 问题，我给你删了

#<Channel id: 1, name: "Hello Focus", introduction: "这里是Focus的频道 😀", channel_type: "picture", intimity: "masses", user_id: 1, created_at: "2017-06-22 15:26:20", updated_at: "2017-06-22 15:26:20">, 5]

最后那个 5 就是 p_count

用最新给你的这个吧，试试

Channel.joins(:posts).select("channels.* , count(posts.channel_id) as p_count ").group("posts.channel_id").order("p_count desc")
1 likes
wwwicbd closed this topic. 23 Jun 14:52
wwwicbd reopened this topic. 23 Jun 14:52
theblock24block #11 June 23, 2017 
top10 = Post.group(:channel_id).order('count(*) desc').limit(10).count
Channel.find top10.keys
You need to Sign in before reply, if you don't have an account, please Sign up first.