CODEWITHSUNDEEP
sqlruby-on-rails-3postgresqlactiverecordsubquery
Nathan Wienert
Nathan Wienert

Reputation: 1623

Rails 3 Applying limit and offset to subquery

I have a query that goes something like this (in song.rb):

def self.new_songs
  Song.where(id: Song.grouped_order_published).select_important_stuff
end

Later on in my app, it is then passed the limit and offset, lets say in the controller:

@songs = Song.new_songs.limit(10).offset(10)

The way my app is structured, I'd like to keep this method of setting things, but unfortunately it is really slow as it is limiting the outer query rather than the subquery.

Is there a way I can expose the subquery such that it receives the limit and offset rather than the outer query?

Edit: I should add I am using postgres 9.2.

Edit 2: The reason why I want to do it in this fashion is I am doing pagination and I need to get the "count" of the total number of rows. So I do something like this:

@songs = Song.new_songs
...
@pages = @songs.count / 10
...
render @songs.limit(params[:page]).offset(0)

If I were to change it somehow, I'd have to redo this entirely (which is in a ton of places). By not limiting it until it's actually called, I can do the count in between and then get just the page at the end. I guess I'm looking more for advice on how this can be done with the inner query, without becoming horribly slow as the database grows.

Upvotes: 4

Views: 2754

Answers (2)

TheConstructor
TheConstructor

Reputation: 4465

I could not try the solution and I am not a ruby expert either, but as far as I understand the problem you would need an object that passes all method-calls but limit and offset onto the full query and store the limited sub_query in the meantime.

It could probably look like this:

class LimitedSubquery < Object

  # sub_query has to be stored so we can limit/offset it
  def initialize(sub_query)
    @sub_query = sub_query
  end

  # Make sure everybody knows we can be used like a query
  def self.respond_to?(symbol, include_private=false)
     super || full_query.respond_to?(symbol, include_private)
  end

  # Missing methods are probably meant to be called on the whole query
  def self.method_missing(method_sym, *arguments, &block)
    if full_query.respond_to?(method_sym)
      full_query.send(method_sym, *arguments, &block)
    else
      super
    end
  end

  # Generate the query for execution
  def self.full_query
    Song.where(id: @sub_query).select_important_stuff
  end

  # Apply limit to sub_query
  def self.limit(*number)
    LimitedSubquery.new(@sub_query.limit(*number))
  end

  # Apply offset to sub_query
  def self.offset(*number)
    LimitedSubquery.new(@sub_query.offset(*number))
  end
end

And than call it like

def new_songs
  LimitedSubquery.new(Song.grouped_order_published)
end

Please edit me if I got something wrong!

Regards

TC

Upvotes: 3

awenkhh
awenkhh

Reputation: 6111

You should consider using the will_paginate gem. This keeps you away form the hazzle to calculate all this by hand ;-)

Upvotes: 0

Related Questions