Limit and offset in Oracle

If you are considering about using some kind of pagination like will_paginate or some pagination of your own flavor, and you are stuck using Oracle i have a solution for you. Oracle is known not to have limit and offset helpers for the select statements. Those two pieces of code that are missing are helping us to paginate properly. Yeas, you can use rownum, and i am using it too, but we have to encapsulate our select a few times, so it won’t make trouble for us(bad data, not ordering properly or something).

I have already made a patch for arel, so it’s embedded in Rails, if you really have use Oracle as your database.

Let’s say that we want to list all the employees from scott.emp table, and order them by empno descending and fetch the records from rows of that data 41 to 50.

SELECT * FROM (
  SELECT raw_sql_.*, rownum raw_rnum_
    FROM (select * from scott.emp order by empno DESC) raw_sql_ )
              WHERE raw_rnum_ between 41 and 50

Yes, i know it looks stupid, but it works, and until Oracle implements limit and offset into it, sadly, this is the only way.

Integrating turbolinks in your Rails application

We are all excited in a new addition to rails plugins family that is turbolinks.

If you have tried to include it in your application, you may have noticed that turbolinks doesn’t trigger your $.ready, because there is no full page reload. As a consequence your javascripts that are bound to $.ready won’t trigger. I will only hack the finished event that is “page:change” which occurs when the page is refreshed. You have other events and their descriptions on the turbolinks github page. Here is a fast hack that i came with. Some of the code is taken from turbolinks library.

First have your $.ready trigger the “page:change” event. This is done, so we can bind everything that is now bound to document.ready to “page:change”

$.ready ->
  triggerEvent "page:change"

triggerEvent = (name) ->
  event = document.createEvent 'Events'
  event.initEvent name, true, true
  document.dispatchEvent event

Now when we have $.ready calling “page:change” we can use it in our application like this

document.addEventListener "page:change", ->
  alert "I am called from $.ready and page:change"

Will Paginate in Rails using remote request

Have you ever wondered why is there no remote option for will_paginate gem? It’s too complex and data dependent to handle all possible situations. I have made a workaround that can help you implement simple ajax pagination for your rails application. I will be replacing the whole yield part here, but you can customize it whatever way you like.

Step 1. Extract your required view into a partial so you have
index.html.erb

<!-- app/views/posts/index.html.erb -->
< %= render "index" %>

_index.html.erb

<!-- app/views/posts/_index.html.erb -->
<h1>Listing posts</h1>
<table id="posts">
  <tr>
    <th>Title</th>
    <th>Body</th>
    <th></th>
    <th></th>
    <th></th>
  </tr>
 < %= render partial: 'post', collection: @posts %>
</table>
< %= will_paginate @posts, remote: true %>
<br />
< %= link_to 'New Post', new_post_path, remote: true %>

Note the remote: true part of the will_paginate call, we will bind the javascript to it in an instance.

2. Add a div surrounding your yield tag in your layout

<!-- app/views/layouts/application.html.erb -->
<div id="content>
  < %= yield %>
</div>

3. Create index.js.erb which will replace the contents of the div with paged table data.

// app/views/posts/index.js.erb
$("div#content").html('< %= escape_javascript(render "index") %>');

4. Bind the will_paginate link click to the rails remote call using coffeescript in

# app/assets/javascripts/posts.js.coffee
$('.pagination[remote=true] a').live 'click', ->
  window.history.pushState(null, 'hi', $(this).attr("href"))
  $.rails.handleRemote($(this))
  return false

We also change the address in the navigation bar with PushState, because it can happen someone will press F5 or something and reload with a different params[:page].

Update: As my coworker Oliver mentioned, i forgot to include responding to js in our controller for the index action

def index
  @posts = Post.paginate(per_page: 8, page: params[:page]
  respond_to do |format|
    format.html
    format.js
  end
end