4

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.

Nathan Wienert
  • 1,623
  • 3
  • 19
  • 25
  • So I figured this out by just not doing a subquery. Still would be an interesting problem to solve though. – Nathan Wienert Nov 04 '12 at 07:27
  • So the problem is that you need to know the total number of matches and get your slice of 10 but you don't want to run the query twice, right? And, of course, you don't want to change more code than you have to. – mu is too short Nov 07 '12 at 07:52

2 Answers2

3

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

TheConstructor
  • 4,285
  • 1
  • 31
  • 52
  • 1
    Didn't check it out, but the logic of it is sound. I ended up finding a way to avoid the subquery, but you deserve the points. – Nathan Wienert Nov 07 '12 at 22:19
  • Thank you! The original query seemed like it could be rewritten, but as stated in your comment you wanted to have a solution to the original problem. – TheConstructor Nov 07 '12 at 23:08
0

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

awenkhh
  • 5,811
  • 1
  • 21
  • 24
  • I don't understand your comment. As far as I understood, you are looking for pagination. If not, please clarify your question. Thank you. Another approach would be to give the new_songs method two parameters limit and offset and then use them for def self.new_songs(limit, offset) Song.where(id: Song.grouped_order_published.limit(limit).offset(offset)).select_important_stuff end Calling @songs = Song.new_songs.limit(10).offset(10) will not work at all. – awenkhh Nov 04 '12 at 09:21
  • I think it was clear enough. If you'd like to see what an answer that actually addresses my question check out the selected one. – Nathan Wienert Nov 07 '12 at 22:19
  • glad that you now have a solution for your problem! – awenkhh Nov 07 '12 at 22:36