CODEWITHSUNDEEP
sqlruby-on-railsfiltertagsransack
Rebecca
Rebecca

Reputation: 128

How do I filter using multiple checkboxes for a single field as an AND query (not OR) using Ransack?

I want users to be able to select multiple different tags in order to filter parks results. For example, they should be able to find a park that has the following 3 tags:

  1. Stroller-friendly
  2. Plenty of shade
  3. Picnic tables

I've implemented a form using Ransack where the user can select multiple tags, but it's treated as an OR query (returning parks with ANY of the tags) rather than an AND query (returning parks with ALL tags).

I think the problem is that all of the checkboxes use the same tags_id_in field. How can I make it work as an AND query instead?

Form

<%= search_form_for @q, url: :parks do |f| %>
  <span class="me-5">
    <% @tags.each do |tag| %>
      <%= f.check_box :tags_id_in, { multiple: true, include_hidden: false, class: "btn-check", id: tag.name.titleize + "Checkbox" }, tag.id %>
      <%= f.label :tags_id_in, tag.name.titleize, class: 'btn btn-outline-secondary border mb-1', for: tag.name.titleize + "Checkbox" %>
    <% end %>
  </span>

  <span class="me-5">
     <%= f.submit 'Apply filters', class: "btn btn-secondary" %>
  </span>

<% end %>

From logs:

Started GET "/en/parks?q%5Btags_id_in%5D%5B%5D=16&q%5Btags_id_in%5D%5B%5D=19&q%5Btags_id_in%5D%5B%5D=21&commit=Apply+filters" for ::1 at 2023-02-05 09:56:58 +0200
Processing by ParksController#index as HTML
  Parameters: {"q"=>{"tags_id_in"=>["16", "19", "21"]}, "commit"=>"Apply filters", "locale"=>"en"}

  Park Count (19.3ms)  SELECT COUNT(*) FROM (SELECT DISTINCT parks.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((32.0908425 - parks.latitude) * PI() / 180 / 2), 2) + COS(32.0908425 * PI() / 180) * COS(parks.latitude * PI() / 180) * POWER(SIN((34.8154259 - parks.longitude) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((parks.longitude - 34.8154259) / 57.2957795), ((parks.latitude - 32.0908425) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing FROM "parks" LEFT OUTER JOIN "tagifications" ON "tagifications"."park_id" = "parks"."id" LEFT OUTER JOIN "tags" ON "tags"."id" = "tagifications"."tag_id" WHERE "tags"."id" IN (16, 19, 21) AND (parks.latitude BETWEEN -899289.5150762305 AND 899353.6967612305 AND parks.longitude BETWEEN -1061478.8079855754 AND 1061548.4388373755 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((32.0908425 - parks.latitude) * PI() / 180 / 2), 2) + COS(32.0908425 * PI() / 180) * COS(parks.latitude * PI() / 180) * POWER(SIN((34.8154259 - parks.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 100000000)) subquery_for_count

Update: I also tried using in_all which I thought should do what I want according to the documentation. However, I'm getting no results if more than one tag is selected. The SQL query generated is ...WHERE...("tags"."id" IN (21) AND "tags"."id" IN (22))....

Looks like the same issue that was reported here: github.com/activerecord-hackery/ransack/issues/108

Update - tried the following per suggested answer below, but then I get 0 results when no tags are selected, and the following error when I try to select a tag:

undefined method 'joins' for nil:NilClass

  def index
    search_coordinates = params[:search_coordinates] || @user_coordinates

    if params[:q] && params[:q][:tags_id_in].present?
      tag_ids = params[:q][:tags_id_in]
      park_ids = @parks.joins(:tags).where(tags: { id: tag_ids }).group('parks.id').having('COUNT(*) = ?', tag_ids.size).pluck(:id)
    end
    
    @parks = @q.result(distinct: true).includes(:visited_users, :favorited_users, :tags, :reviews).near(search_coordinates, 100000000).where(id: park_ids).paginate(page:params[:page], :per_page => 24)
    @tags = Tag.all
  end

Upvotes: 0

Views: 156

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35553

The generated SQL shows the IN (16,19,21) is used and this is treated as a series of OR conditions.

To achieve the equivalent of a series of AND conditions consider using an additional group by and count e.g.

SELECT parks.id
FROM parks
INNER JOIN parks_tags ON parks.id = parks_tags.park_id
INNER JOIN tags ON tags.id = parks_tags.tag_id
WHERE tags.id IN (16,19,21)
GROUP BY parks.id
HAVING COUNT(DISTINCT tags.id) = 3

i.e. you have to get all 3 tag rows to get a count of 3. This of course assumes you would not have any tag repeated for a park.

The following is unverified but in the controller code use logic like this:

# In the ParksController
def index
  @q = Park.ransack(params[:q])
  @parks = @q.result

  # Check if @parks is nil
  if @parks.nil?
    # Handle the case where @parks is nil
  else
    # If tags are selected
    if params[:q] && params[:q][:tags_id_in].present?
      # Find parks that have all of the selected tags
      tag_ids = params[:q][:tags_id_in]
      park_ids = @parks.joins(:tags).where(tags: { id: tag_ids }).group('parks.id').having('COUNT(DISTINCT tags.id) = ?', tag_ids.size).pluck(:id)
      @parks = Park.where(id: park_ids)
    end
  end

  # etc.
end

nb: Included test for @parks being nil, and I also changed from count(*) to count(DISTINCT tags.id)

Upvotes: 0

Related Questions