如何用Ransack实现多复选框单字段AND查询过滤公园标签？

我需要让用户选择多个标签来过滤公园，比如找到同时具备以下3个标签的公园：

• 适合婴儿车（Stroller-friendly）
• 树荫充足（Plenty of shade）
• 配有野餐桌（Picnic tables）

已经用Ransack做了选择多标签的表单，但现在是OR查询（返回有任一标签的公园），不是想要的AND查询（返回具备所有选中标签的公园）。我觉得问题出在所有复选框都用同一个tags_id_in字段，该怎么改成AND查询？

表单代码 #

<%= 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 %>

请求日志 #

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

尝试过程中的问题 #

用in_all无效 #

我试过用in_all，按文档说明它应该实现AND查询，但选多个标签时没有结果，生成的SQL是...WHERE...("tags"."id" IN (21) AND "tags"."id" IN (22))...，这是个已知问题。

手动筛选代码报错 #

按建议写了以下代码，但未选标签时返回0条结果，选标签时报错：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

方法1：修复控制器手动筛选逻辑 #

问题出在@parks在赋值前是nil，调用joins会报错；另外未选标签时park_ids为nil，where(id: nil)会返回空结果。修改后的控制器代码：

def index
  search_coordinates = params[:search_coordinates] || @user_coordinates
  # 先初始化基础查询
  base_query = Park.includes(:visited_users, :favorited_users, :tags, :reviews).near(search_coordinates, 100000000)
  
  if params[:q] && params[:q][:tags_id_in].present?
    tag_ids = params[:q][:tags_id_in]
    # 找到同时拥有所有选中标签的公园ID
    park_ids = base_query.joins(:tags)
                         .where(tags: { id: tag_ids })
                         .group('parks.id')
                         .having('COUNT(DISTINCT tags.id) = ?', tag_ids.size)
                         .pluck(:id)
    # 过滤基础查询
    base_query = base_query.where(id: park_ids)
  end

  # 结合Ransack的其他筛选（如果有的话）
  @q = base_query.ransack(params[:q].except(:tags_id_in))
  @parks = @q.result(distinct: true).paginate(page: params[:page], per_page: 24)
  @tags = Tag.all
end

方法2：自定义Ransack Predicate #

如果想继续用Ransack的表单语法，可以自定义一个Predicate来实现AND逻辑：

在app/models/park.rb中添加：

class Park < ApplicationRecord
  # ... 其他关联和代码
  ransacker :has_all_tags, formatter: proc { |tag_ids|
    ids = Park.joins(:tags)
              .where(tags: { id: tag_ids })
              .group('parks.id')
              .having('COUNT(DISTINCT tags.id) = ?', tag_ids.size)
              .pluck(:id)
    ids.present? ? ids : nil
  } do |parent|
    parent.table[:id]
  end
end

修改表单，把tags_id_in换成has_all_tags_eq：

<%= search_form_for @q, url: :parks do |f| %>
  <span class="me-5">
    <% @tags.each do |tag| %>
      <%= f.check_box :has_all_tags_eq, { multiple: true, include_hidden: false, class: "btn-check", id: tag.name.titleize + "Checkbox" }, tag.id %>
      <%= f.label :has_all_tags_eq, 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 %>

这样控制器可以保持简洁的Ransack用法，同时实现多标签AND过滤。

内容的提问来源于stack exchange，提问作者Rebecca