Ransack: Two filters each work on their own but not together, PG::UndefinedTable error-CodePudding

In my Rails 7 app I have parks and users. Users can mark parks as favorite and visited. I want to allow the currently logged in user to filter parks based on both whether they've favorited the park, AND whether they've visited the park.

Currently, the filters work if I only include one of them. But if I add both filters to the form, I get the following error:

ActiveRecord::StatementInvalid in Parks#index

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "favorited_users_parks"
LINE 1: ...rks"."id" AND NOT ("users"."id" NOT IN (44))) AND "favorited...

Note - this seems very very similar to the issues described here, but I'm using Rails 7 and in this thread they claim the issue was resolved after updating to Rails 6.1.0: https://github.com/activerecord-hackery/ransack/issues/1119

And this thread seems to have some solutions that worked for other people but I don't understand how to use joins and ransacks well enough to understand how it applies to my own code: https://github.com/activerecord-hackery/ransack/issues/542

Here is the code:

views/parks/filters.erb

<div >
   <%= f.label :favorited_users, 'Saved to favorites' %>
   <%= f.check_box :favorited_users_id_in, { class: "form-check-input" }, current_user&.id %>
</div>

<div >
   <%= f.label :visited_users_id, 'Not yet visited' %>
   <%= f.check_box :visited_users_id_not_in, { class: "form-check-input" }, current_user&.id %>
</div>

controllers/parks_controller.rb

class ParksController < ApplicationController
  def index
    @parks = @q.result(distinct: true).paginate(page:params[:page], :per_page => 24)
  end
end

models/park.rb

class Park < ApplicationRecord
  has_many :visits, dependent: :destroy
  has_many :visited_users, through: :visits, source: :user

  has_many :favorites, dependent: :destroy
  has_many :favorited_users, through: :favorites, source: :user
end

models/user.rb

class User < ApplicationRecord
  has_many :visits, dependent: :destroy
  has_many :visited_parks, through: :visits, source: :park

  has_many :favorites, dependent: :destroy
  has_many :favorited_parks, through: :favorites, source: :park
end

models/favorite.rb

class Favorite < ApplicationRecord
  belongs_to :user
  belongs_to :park
end

models/visit.rb

class Visit < ApplicationRecord
  belongs_to :user
  belongs_to :park
end

CodePudding user response：

I haven't looked too much into it, seems the issue is still present. Solution is just sitting in a comment:
https://github.com/activerecord-hackery/ransack/issues/542#issuecomment-245700688

def index
  @q = Park.ransack(params[:q]) # this is just an object for the form
  @parks = search(Park.all)     # this is for actual search results
end

private

def search(collection)
  results = collection
  params[:q]&.each { |k, v| results = results.ransack(k => v).result }
  results
end

Just changing the order of the inputs also works.

You might want to skip hidden input for checkboxes, otherwise you send 0 when unchecked:

#                                         vvvvvvvvvvvvvvvvvvvvv
<%= f.check_box :favorited_users_id_in, { include_hidden: false, class: "form-check-input" }, current_user&.id %>

# or like this                                                                           vvv
<%= f.check_box :favorited_users_id_in, { class: "form-check-input" }, current_user&.id, nil %>