新手问题 关于多次查询排序输出结果的问题

sandwind · 2015年07月20日 · 最后由 sandwind 回复于 2015年07月20日 · 2489 次阅读

首先在 model 中定义类方法进行查询

class Post < ActiveRecord::Base
  belongs_to :user
  has_many  :photos
  # belongs_to :sub
  accepts_nested_attributes_for :photos, allow_destroy: true

  def self.main(main)
    where(main: main)
  end

  def self.sub(sub)
   where(sub: sub)
  end

  def self.create_before(date)
    where("created_at < ?", date)
  end 

  def self.create_after(date)
    where("created_at > ?", date)
  end
end

要查询的是在以时间为比较查询这个时间之前(后)各 N 条数据,并以降序的顺序来返回 json 数组,接口中调用如下:

resource :posts do 
   desc 'post_list'
   params do
      requires :access_token, :type => String, :desc => "token"
      requires :main,   :type => String, :desc => "main"
      requires :sub,    :type => String, :desc => "sub"
      requires :date,   :type => DateTime, :desc => "date"
      requires :count,  :type => Integer, :desc => "count"
   end

   post 'posts_lists' do

       if Post.count == 0
         error!({status: false, :errors => ["posts count is zero"] }, 201) 
       end

       begin
         if params[:count] > 0
            posts = Post.main(params[:main]).sub(params[:sub]).create_after(params[:date]).limit(params[:count])
         else
           posts = Post.main(params[:main]).sub(params[:sub]).create_before(params[:date]).order(created_at: :desc).limit(params[:count].abs)
         end

         status(201)
         {
           status: true, 
           errors:[],
           posts: posts.select(:id,:title,:created_at,:previewurl,:replycnt,:upcount,:downcount)
         } 
       rescue ActiveRecord::RecordNotFound 
         error!({status: false, :errors => ["not find posts"] }, 201) 
       end 
   end

发现问题这里当 当我查询查询

posts = Post.main(params[:main]).sub(params[:sub]).create_after(params[:date]).limit(params[:count])

当 count > 0 查询结果是出来是以按照升序的方式排列 但是当我把查询结果以降序排序:

{
  "posts": [
    {
      "id": 21,
      "title": "Sed excepturi explicabo fugit est sapiente culpa alias iusto.",
      "created_at": "2015-07-19T11:38:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 20,
      "downcount": 20,
      "replycnt": 20
    },
    {
      "id": 22,
      "title": "Quibusdam modi impedit veniam rem quisquam consectetur odit aut.",
      "created_at": "2015-07-19T11:39:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 21,
      "downcount": 21,
      "replycnt": 21
    },
    {
      "id": 23,
      "title": "Fuga consequuntur reiciendis unde vero quidem non inventore.",
      "created_at": "2015-07-19T11:40:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 22,
      "downcount": 22,
      "replycnt": 22
    },
    {
      "id": 24,
      "title": "Eius earum ipsum harum et rerum et officia totam.",
      "created_at": "2015-07-19T11:41:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 23,
      "downcount": 23,
      "replycnt": 23
    },
    {
      "id": 25,
      "title": "Et deleniti voluptas quo inventore maiores.",
      "created_at": "2015-07-19T11:42:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 24,
      "downcount": 24,
      "replycnt": 24
    }
  ]
}
posts = Post.main(params[:main]).sub(params[:sub]).create_after(params[:date]).limit(params[:count]).order(created_at: :desc)

输出结果则是从整张表最上面查找出来结果进行排序,这个结果就完全跟之前查询目的不一样,而是排序后再进行时间比较过后的查询结果 结果如下:

{
  "status": true,
  "errors": [],
  "posts": [
    {
      "id": 100,
      "title": "Enim repudiandae sed quaerat dolorum soluta pariatur eum velit cupiditate.",
      "created_at": "2015-07-19T12:57:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 99,
      "downcount": 99,
      "replycnt": 99
    },
    {
      "id": 99,
      "title": "Eum aut eius repellat mollitia ullam rerum perspiciatis veritatis doloremque.",
      "created_at": "2015-07-19T12:56:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 98,
      "downcount": 98,
      "replycnt": 98
    },
    {
      "id": 98,
      "title": "Molestias fugiat fugit incidunt doloribus.",
      "created_at": "2015-07-19T12:55:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 97,
      "downcount": 97,
      "replycnt": 97
    },
    {
      "id": 97,
      "title": "Maxime maiores voluptatum deleniti assumenda aut dicta.",
      "created_at": "2015-07-19T12:54:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 96,
      "downcount": 96,
      "replycnt": 96
    },
    {
      "id": 96,
      "title": "Sint autem nemo facere ad odit quidem ullam voluptatem.",
      "created_at": "2015-07-19T12:53:38.000Z",
      "previewurl": "http://filestorageapp.b0.upaiyun.com/uploads/user_profile/avatar/3/normal_avatar.jpg",
      "upcount": 95,
      "downcount": 95,
      "replycnt": 95
    }
  ]
}

我不知道有什么排序接口不影响最后查询结果来进行排序 求大神指点迷津

def self.main(main)
   where(main: main)
 end

 def self.sub(sub)
  where(sub: sub)
 end

 def self.create_before(date)
   where("created_at < ?", date)
 end 

 def self.create_after(date)
   where("created_at > ?", date)
 end

首先这些方法都改成 scope 吧,至于排序你先搞清楚要的哪种结果吧?得到 21-25 的结果集什么场景用到了,降序得到 96-100 从排序的意义上来说就应如此

@xxqfamous 其实我就是想把 21-25 这个结果 进行降序输出

posts.sort!{|a,b| b.id <=>a.id }

@xxqfamous 这还是用 scope 封装起来用?

#4 楼 @sandwind 你可以对比下你现在的写法和 scope 写法控制台 sql 日志,多看看文档,理解 scope 用途意义

@xxqfamous 好的谢谢指导
posts.sort!{|a,b| b.id <=>a.id } 封装成这样

scope :sort  xxxxxxx

不懂的是 |a,b| 这个参数是怎样传递进去的?

直接posts.reverse不就反过来了吗

贴一下你这条语句执行的结果:

Post.main(params[:main]).sub(params[:sub]).create_after(params[:date]).limit(params[:count]).order(created_at: :desc).to_sql

@xxqfamous posts.sort!{|a,b| b.id <=>a.id } 这个怎么封装 没搞定 求指教

@hw676018683 没有用呢 还是返回 升序

#10 楼 @sandwind posts = Post.main(params[:main]).sub(params[:sub]).create_after(params[:date]).limit(params[:count]).reverse 这么写的?

@hw676018683 恩 就是这么写的,输出的还是结果不变

#12 楼 @sandwind 我测试是可以的,我也不知道问题出在哪

@hw676018683 对了 我找到问题了

需要 登录 后方可回复, 如果你还没有账号请 注册新账号