Sorting / Grouping records for pagination with many association-based restrictions

I'm working on an application which acts as a places directory and (currently, but 开发者_StackOverflowmaybe not always) allows filtering of a particular businesses' venues based upon 1 city, 1 neighborhood, and 1 category. Categories can be nested, and therefore they have a parent_id. The associations can be understood by looking at the model code below (everything is fairly straightforward). So far, everything is running smoothly, but there's one obstactle left.

First, some important notes that will affect answers.

1. I am using will_paginate for paginating a venues association. That being said, I can't paginate on an array. Well, I could, but performance would be an issue down the line.

2. I am using a Tag model as a link object between a business and it's associated categories. Currently, the interface is only set up to allow one category to be attached to any given business, and at least one must be attached. I plan to later expand the interface for the public-facing pages to allow filtering by multiple categories, so changes cannot be made to this part of the application structure unless there is a much, much better way of accomplishing this.

3. A possible solution which might (hopefully) exist is aliasing a table join in Arel or a scope allowing categories to self-join on themselves to get at the parent. Currently, I would find it acceptable if the solution made the assumption that there will be no more than 1 level of nesting. This sacrifice is a last resort, though, as it would really put a damper on the functionality I want to introduce in the future.

4. Related to the last point, I have looked at will_paginate/array, but was scared off by their disclaimer ("If you know what you’re doing and really need to paginate Arrays, require this feature explicitly"). Remembering the performance nightmares I've run into in the past when trying to roll my own pagination plugin, I'd like to avoid this, unless somebody can adequately explain to me the performance ramifications of such a solution.

5. This site needs to be able to take a pounding. Right now, everything is cached, and database non-cache hits are relatively few and far between. It must stay that way.

Now, the question I wish to pose.

Background: Some of the public-facing views' design specs requires that all venues from the current city be displayed in a list and grouped by parent category (no subcategories are to be displayed, but all venues who's businesses' tags belong to a subcategory should be also grouped with the venues who's tags belong to the parent). These venues are then sorted by parent category, sorting secondarily on the name of the business which the venue belongs to. This needs to be a flat association, which is then fed into will_paginate. This venues ActiveRecord query, which I will refer to hereinafter as @venues, is then dumped into JSON, cached, and rendered to the page.

Question: How do I construct @venues with the specified grouping/ordering so these venues can be properly paginated and displayed according to the specifications of this interface?

app/models/business.rb:

# Fields: id, name, description, keywords, ...
class Business < ActiveRecord::Base
  has_many :tags, :dependent => :destroy
  has_many :categories, :through => :tags

  has_many :venues, :dependent => :destroy

  has_many :cities, :through => :venues
  has_many :neighborhoods, :through => :venues
end

app/models/venue.rb:

# Fields: id, business_id, city_id, neighborhood_id, ...
class Venue < ActiveRecord::Base
  belongs_to :business
  belongs_to :city
  belongs_to :neighborhood
end

app/models/tag.rb:

# Fields: id, category_id, business_id, ...
class Tag < ActiveRecord::Base
  belongs_to :business
  belongs_to :category
  belongs_to :venue
end

app/models/category.rb:

# Fields: id, parent_id, name, description, ...
class Category < ActiveRecord::Base
  has_many :tags, :dependent => :destroy
end

app/models/city.rb:

# Fields: id, name, description, ...
class City < ActiveRecord::Base
  has_many :neighborhoods, :dependent => :destroy
  has_many :venues
end

app/models/neighborhood.rb:

# Fields: id, city_id, name, description
class Neighborhood < ActiveRecord::Base
  belongs_to :city
  has_many :venues
  has_many :businesses, :through => :venues
end

This was one doozey to explain, and I can provide more information if need be.

P.S. Using Rails 3.0.9 for this application with MySQL.

P.S.S. I'm also interested in patterns or gems which simplify this sort of filtering based on the possible values of multiple nested associations' fields. I will grant upvote+accept+bounty to whomever can provide an exact solution using a Nested set model gem such as Awesome Nested Set for grouping/sorting/paginating in this manner.

---

no subcategories are to be displayed, but all venues who's businesses' tags belong to a subcategory should be also grouped with the venues who's tags belong to the parent.

as Xavier said in the comment you should use a nested set model. I am using this one :

https://github.com/collectiveidea/awesome_nested_set

And that gives me the ability to paginate and sort as usual:

module Category
  extend ActiveSupport::Concern
  included do
    belongs_to :category
    scope :sorted, includes(:category).order("categories.lft, #{table_name}.position")
  end

  module ClassMethods
    def tree(category=nil)
      return scoped unless category
      scoped.includes(:category).where([
        "categories.lft BETWEEN ? AND ?",category.lft, category.rgt
      ])
    end
  end # ClassMethods
end

#then with some category
@animals = Animal.tree(@mamal_category)

that will get you all herbivores, carnivores, omnivores etc etc and sub sub categories in one sql call and it's easilly paginatable, sortable since it's a scope.

Also see : get all products of category and child categories (rails, awesome_nested_set)