Rails - Mar 2014 - order_query finds next / previous Active Record(s) in one query

order_query

order_query gives you next or previous records relative to the current
one efficiently.

For example, you have a list of items, sorted by priority. You have
10,000 items! If you are showing the user a single item, how do you
provide buttons for the user to see the previous item or the next item?

You could pass the item's position to the item page and use OFFSET in
your SQL query. The downside of this, apart from having to pass a number
that may change, is that the database cannot jump to the offset; it has
to read every record until it reaches, say, the 9001st record. This is
slow. Here is where order_query comes in!

order_query uses the same ORDER BY query, but also includes a WHERE
clause that excludes records before (for next) or after (for prev) the
current one.


    class Post < ActiveRecord::Base
      include OrderQuery
      order_query :order_list, [
        [:pinned, [true, false]],
        [:published_at, :desc],
        [:id, :desc]
      ]
    end


Now to use it:

    # get the order object, scope default: Post.all
    p = Post.find(31).order_list(scope) #=>
OrderQuery::RelativeOrder<...>
    p.before         #=> ActiveRecord::Relation<...>
    p.previous       #=> Post<...>
    p.position   #=> 5
    p.next       #=> Post<...>
    p.after      #=> ActiveRecord::Relation<...>

The magic is in the generated WHERE clause that excludes all the records
after / before the current one, such as:

    -- post: pinned=true published_at='2014-03-21 15:01:35.064096' id=9
    SELECT "posts".* FROM "posts"  WHERE
      ("posts"."pinned" = 'f' OR
       "posts"."pinned" = 't' AND (
          "posts"."published_at" < '2014-03-21
15:01:35.064096' OR
          "posts"."published_at" = '2014-03-21
15:01:35.064096' AND
"posts"."id" < 9))
    ORDER BY
      "posts"."pinned"='t' DESC,
      "posts"."pinned"='f' DESC,
"posts"."published_at" DESC,
"posts"."id" DESC
    LIMIT 1

See more on Github:
https://github.com/glebm/order_query

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/df61e9af841a1131df6f21ed0301c5f8%40ruby-forum.com.
For more options, visit https://groups.google.com/d/optout.