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

wwwicbd · 发布于 2017年06月23日 · 最后由 theblock24block 回复于 2017年06月23日 · 426 次阅读
24195

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
共收到 8 条回复
2099

我能想到的 3 种方案

  1. 像你上面这样
  2. 建立一个视图
  3. 缓存 channel 的 post 总数,存在 channel 表里的一个字段
17671

加个计数器吧,省的麻烦

3楼 已删除
96
Channel.joins(:posts).select("channels.* , count(posts.channel_id) as p_count ").group("posts.channel_id").order("p_count desc")

p_count 可以. 出来

24195

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

96

counter_cache => true

24195
32will_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>
96
24195wwwicbd 回复

你用的是我第一个给你的查询吧, 那个有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")
24195 wwwicbd 关闭了讨论 06月23日 14:52
24195 wwwicbd 重新开启了讨论 06月23日 14:52
17727
top10 = Post.group(:channel_id).order('count(*) desc').limit(10).count
Channel.find top10.keys
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册