获取每个组的最新n条记录

时间:2021-08-04 12:20:04

I have two models: topic comment

我有两个模型:主题评论

topic has_many comments

主题has_many评论

How i can get the last 4 comments for each topic?

我怎么能得到每个主题的最后4条评论?

I tried with this but is very slow:

我试过这个但很慢:

Comment.
      joins("INNER JOIN (SELECT m1.id,m1.topic_id, COUNT(m2.id) as c FROM multimedias as m1 LEFT JOin multimedias as m2 ON 
        m1.topic_id = m2.topic_id  AND m1.id < m2.id 
        WHERE m1.topic_id IS NOT NULL GROUP BY m1.id, m1.topic_id HAVING c < 4
        ORDER by m1.topic_id, c desc) as m3 ON m3.id = multimedias.id").
      where([ "multimedias.topic_id IN (?)", topics_id ]) 

3 个解决方案

#1


0  

You can try to use last method

您可以尝试使用last方法

#2


0  

The #last method takes an integer as an argument, so you can just use Comment.last(4)

#last方法以整数作为参数,因此您只需使用Comment.last(4)

#3


0  

ActiveRecord::FinderMethods#Last

#last has an optional argument that allows you to specify the last N records, like this:

#last有一个可选参数,允许您指定最后N个记录,如下所示:

Comment.
      joins("INNER JOIN (SELECT m1.id,m1.topic_id, COUNT(m2.id) as c FROM multimedias as m1 LEFT JOin multimedias as m2 ON 
        m1.topic_id = m2.topic_id  AND m1.id < m2.id 
        WHERE m1.topic_id IS NOT NULL GROUP BY m1.id, m1.topic_id HAVING c < 4
        ORDER by m1.topic_id, c desc) as m3 ON m3.id = multimedias.id").
      where([ "multimedias.topic_id IN (?)", topics_id ]).
      last(4)

#1


0  

You can try to use last method

您可以尝试使用last方法

#2


0  

The #last method takes an integer as an argument, so you can just use Comment.last(4)

#last方法以整数作为参数,因此您只需使用Comment.last(4)

#3


0  

ActiveRecord::FinderMethods#Last

#last has an optional argument that allows you to specify the last N records, like this:

#last有一个可选参数,允许您指定最后N个记录,如下所示:

Comment.
      joins("INNER JOIN (SELECT m1.id,m1.topic_id, COUNT(m2.id) as c FROM multimedias as m1 LEFT JOin multimedias as m2 ON 
        m1.topic_id = m2.topic_id  AND m1.id < m2.id 
        WHERE m1.topic_id IS NOT NULL GROUP BY m1.id, m1.topic_id HAVING c < 4
        ORDER by m1.topic_id, c desc) as m3 ON m3.id = multimedias.id").
      where([ "multimedias.topic_id IN (?)", topics_id ]).
      last(4)