title: Rails complex filter with pagination
body: <p>I am working on a project in which I have to show groups.</p>
  <p>Following models are involved</p>
  <p><strong>Group</strong></p>
  <pre><code>class Group &lt; ApplicationRecord
    has_many :user_groups, dependent: :destroy
    has_many :users, through: :user_groups
  
    belongs_to :creator, :class_name =&gt; &quot;User&quot;, :foreign_key =&gt; &quot;creator_id&quot;
  
    belongs_to :group_status
  
    has_many :matches, dependent: :destroy
    has_many :liked, :through =&gt; :matches, :source =&gt; :macther
  
    has_many :inverse_matches , :class_name =&gt; &quot;Match&quot; , :foreign_key =&gt; &quot;matcher_id&quot;, dependent: :destroy
    has_many :liked_by, :through =&gt; :inverse_matches, :source =&gt; :group
  
    enum status: %w[active inactive]
  
    reverse_geocoded_by :latitude, :longitude
  end
  </code></pre>
  <p><strong>User</strong></p>
  <pre><code>class User &lt; ApplicationRecord
    
    has_one :setting, dependent: :destroy
  
    has_many :friendships, dependent: :destroy #, :class_name =&gt; &quot;Friendship&quot;, :foreign_key =&gt; 'user_id'
    has_many :invited, :through =&gt; :friendships, :source =&gt; :friend
  
    has_many :inverse_friendships , :class_name =&gt; &quot;Friendship&quot; , :foreign_key =&gt; &quot;friend_id&quot;, dependent: :destroy
    has_many :invited_by, :through =&gt; :inverse_friendships, :source =&gt; :user
  
    has_many :user_groups, dependent: :destroy
    has_many :groups, through: :user_groups
  
    has_many :group_creations, :foreign_key =&gt; :creator_id, :class_name =&gt; 'Group'
    has_many :group_statuses
  
    
    has_many :matches, dependent: :destroy
    
    enum gender: %w[male female other]
  end
  </code></pre>
  <p><strong>UserGroup</strong></p>
  <pre><code>class UserGroup &lt; ApplicationRecord
      belongs_to :user
      belongs_to :group
  
      enum status: %w[pending joined reported]
      attr_accessor :invite_id
  end
  </code></pre>
  <p><strong>Match</strong></p>
  <pre><code>class Match &lt; ApplicationRecord
    belongs_to :group
    belongs_to :matcher, :class_name =&gt; &quot;Group&quot;
  
    belongs_to :user
    enum status: %w[like match unlike block blocked report reported]
  end
  </code></pre>
  <p>First show groups according to user's preference like age, gender and group member's count and than show rest of users.</p>
  <p>What I am doing is first exclude all groups which don't meet user preference  and show remaining ones, than show those which don't meet user's preferance.</p>
  <p>This is how algo looks like</p>
  <p>1- find groups which don't match group member's count</p>
  <pre><code>group_member_count_check = Group.joins(:users).joined_group.group('groups.id')
                                          .having(&quot;count(groups.id) != #{setting.group_member_count}&quot;).pluck(:id)
  </code></pre>
  <p>2- find groups which don't meet gender preference</p>
  <pre><code>if setting.male_only
    gender_check = Group.joins(:users).joined_group.where('users.gender !=?', 0).pluck(:id)
  elsif setting.female_only
    gender_check = Group.joins(:users).joined_group.where('users.gender !=?', 1).pluck(:id)
  end
  </code></pre>
  <p>3- than remove groups which contain blocked user's by current user</p>
  <pre><code>blocked_users = current_user.friendships.where(friendships: {status: 2}).pluck(:friend_id) + 
                  current_user.inverse_friendships.where(friendships: {status: 2}).pluck(:user_id)
  blocked_group_ids = Group.joins(:users).joined_group.where(users: {id: blocked_users}).pluck(:id)
  </code></pre>
  <p>4- remove already liked/disliked groups</p>
  <pre><code>already_liked_or_matched_id = (group.matches.where(status: [0,1,3,4,5,6]).pluck(:matcher_id)  
        + current_user.matches.where(status: [2]).pluck(:matcher_id)).uniq
  </code></pre>
  <p><strong>Now first return groups excluding above extracted groups</strong></p>
  <pre><code>groups = Group.includes(:group_status, :users)
                        .where(status: 'active', spot_light_enabled: false)
                        .where.not(id: group_member_count_check +  gender_check +blocked_group_ids + already_liked_or_matched_id  )
                        .near([latitude, longitude], setting.max_distace, order: 'random()').page(params[:page]).per(params[:per_page])
  </code></pre>
  <p><strong>If no preferred group remaining than show other groups</strong></p>
  <pre><code>if groups.length.zero?
            groups = Group.includes(:group_status, :users).where(status: 'active')
                                .where(id: gender_check + group_member_count_check)
                                .where.not(id: blocked_group_ids + already_liked_or_matched_id )
                                .near([latitude, longitude], 10000, order: 'random()').page(params[:page]).per(params[:per_page])
          end
  </code></pre>
  <p>Than response is like this</p>
  <pre><code>{ 
     groups: groups, 
     pagination: pagination_dict(Group.where.not(id: blocked_group_ids ).page(params[:page]).per(params[:per_page]))
  }
  </code></pre>
  <p><strong>SCHEMAS</strong></p>
  <pre><code>  create_table &quot;users&quot;, id: :serial, force: :cascade do |t|
      t.string &quot;phone_number&quot;
      t.string &quot;user_name&quot;
      t.string &quot;authentication_token&quot;
      t.string &quot;first_name&quot;
      t.string &quot;last_name&quot;
      t.text &quot;bio&quot;
      t.date &quot;dob&quot;
      t.integer &quot;age&quot;
      t.integer &quot;gender&quot;
      t.integer &quot;group_id&quot;
      t.decimal &quot;latitude&quot;, precision: 15, scale: 6
      t.decimal &quot;longitude&quot;, precision: 15, scale: 6
      t.string &quot;city&quot;
      t.string &quot;state&quot;
      t.boolean &quot;creator&quot;, default: false
      t.boolean &quot;is_premium&quot;, default: false
      t.datetime &quot;created_at&quot;, null: false
      t.datetime &quot;updated_at&quot;, null: false
      t.integer &quot;instant_match_allow&quot;, default: 1
      t.integer &quot;spot_light_allow&quot;, default: 1
      t.boolean &quot;is_blocked&quot;, default: false
      t.boolean &quot;is_new&quot;, default: true
      t.index [&quot;authentication_token&quot;], name: &quot;index_users_on_authentication_token&quot;
      t.index [&quot;phone_number&quot;], name: &quot;index_users_on_phone_number&quot;, unique: true
      t.index [&quot;user_name&quot;], name: &quot;index_users_on_user_name&quot;, unique: true
    end
  
    create_table &quot;groups&quot;, force: :cascade do |t|
      t.integer &quot;group_status_id&quot;
      t.string &quot;name&quot;
      t.integer &quot;status&quot;, default: 0
      t.decimal &quot;latitude&quot;, precision: 15, scale: 6
      t.decimal &quot;longitude&quot;, precision: 15, scale: 6
      t.string &quot;city&quot;
      t.string &quot;state&quot;
      t.datetime &quot;created_at&quot;, null: false
      t.datetime &quot;updated_at&quot;, null: false
      t.integer &quot;creator_id&quot;
      t.integer &quot;instant_match_allow&quot;, default: 1
      t.integer &quot;spot_light_allow&quot;, default: 1
      t.boolean &quot;spot_light_enabled&quot;, default: false
      t.datetime &quot;spot_light_time&quot;
      t.integer &quot;place_id&quot;
      t.index [&quot;group_status_id&quot;], name: &quot;index_groups_on_group_status_id&quot;
    end
  
    create_table &quot;user_groups&quot;, force: :cascade do |t|
      t.integer &quot;user_id&quot;
      t.integer &quot;group_id&quot;
      t.boolean &quot;creator&quot;, default: false
      t.integer &quot;status&quot;, default: 0
      t.datetime &quot;created_at&quot;, null: false
      t.datetime &quot;updated_at&quot;, null: false
    end
  
    create_table &quot;matches&quot;, force: :cascade do |t|
      t.integer &quot;group_id&quot;
      t.integer &quot;matcher_id&quot;
      t.integer &quot;status&quot;
      t.integer &quot;user_id&quot;
      t.datetime &quot;created_at&quot;, null: false
      t.datetime &quot;updated_at&quot;, null: false
      t.integer &quot;chat_id&quot;
    end
  </code></pre>
  <p><strong>NOW ISSUES</strong></p>
  <p>1- Major one that there are too many queries (I have to place some more checks yet exclude already shown groups etc.)</p>
  <p>2- pagination don't work according to expectation (skip some pages due switch from preferred to other groups)</p>
  <p>what would be the preferred way to manage this?</p>