Rails gem Ransack ->使用“is null"和特定值搜索“or condition"(field=x OR field IS NULL) [英] Rails gem Ransack -> search 'or condition' with 'is null' and a specific value (field=x OR field IS NULL)

问题描述

我为我们的支持者制作了一个支持票系统 - 使用 ruby​​ on rails (Ruby 1.9.3 Rails 3.2) 进行编程

有一个与用户(支持者)的 belongs_to 关联的票证模型.

我使用 Ernie 的 gem Ransack 进行搜索.当支持者搜索自己的票(由 ticket_controller.index 处理)时，他还应该在搜索结果(视图/票/索引)中看到未关联的票(结合其他搜索条件，例如日期"或其他内容)

(这样他就可以拿开票了)

预期的 SQL 语句:

SELECT * FROM ticket WHERE ... AND (user_id=5 OR user_id IS NULL) AND ...

我在 index-view 的搜索表单中尝试了 user_id_null_or_user_id_eq - 但这不起作用(没有方法错误)

如何制作自定义谓词(或ransacker)来解决此问题?(感谢@Magnuss 的评论)

很遗憾，我对 Arel 没有经验

类似于:

ransacker :user_null_or_eq do...结尾

在门票模型中.

我希望这能更详细地解释问题.

感谢您的帮助.

解决方案

我也不得不解决这个问题，@TomG 的回答帮助很大！你真的不需要为这个问题创建一个ransacker，它可以通过使用ransack的build_grouping方法来修复(这在任何地方都没有记录......)并用'or'条件放入你需要的条件.

我用它来搜索要添加到笼子中的物品，但只想向用户显示我的笼子中尚未存在的物品(也未分配给任何其他笼子)，因此在我的控制器中:

 @search = Cage.ransack(params[:q])@search.build_grouping({:m => 'or', :storage_id_not_eq => @cage_item.id, :storage_id_null => true})@results_count = @search.result(:distinct => true).length@items = @search.result(:distinct => true).paginate(:per_page => 20, :page => params[:page] || 1)@search.build_condition 如果@search.conditions.empty?@search.build_sort 如果@search.sorts.empty?

所以当我打印正在运行的查询时，它会显示:

 @search.result.to_sql"SELECT `specimens`.* FROM `specimens` WHERE (`specimens`.`deleted_at` 为 NULL) AND ((`specimens`.`storage_id` != 183 OR `specimens`.`storage_id` IS NULL))"

如果我添加名称查询:

@search.result.to_sql"SELECT `specimens`.* FROM `specimens` WHERE (`specimens`.`deleted_at` 为 NULL) AND ((`specimens`.`name` LIKE '%root%' AND (`specimens`.`storage_id` !=第 183 章

希望这可以帮助您或其他人在掠夺条件下使用 OR 而不是基本的 AND 时遇到问题.

I made a support ticket system for our supporters - programmed with ruby on rails (Ruby 1.9.3 Rails 3.2)

There is a ticket model with a belongs_to association to users (supporter).

I use Ernie's gem Ransack for searching. When the supporter searches his own tickets (handled by tickets_controller.index) he should also see the unassociated tickets in the search result (views/tickets/index) (in combination with the other serach conditions eg. "date" or something else)

(So that he can take an open ticket)

expected SQL statement:

SELECT * FROM tickets WHERE ... AND (user_id=5 OR user_id IS NULL) AND ... 

I tried user_id_null_or_user_id_eq in the search form of the index-view - but that does not work (no method error)

How can I make a custom predicate (or ransacker) to solve this problem? (Thanks @Magnuss for this comment)

Unfortunately, I have no experience with Arel

Something like:

ransacker :user_null_or_eq do
    ...
end

in the tickets model.

I hope this explains the problem in more detail.

Thanks for any help.

解决方案

I also had to figure this out and @TomG answer helped a lot! You really don't need to create a ransacker for this issue, it an be fixed by using the build_grouping method of ransack (that is not documented anywhere...) and put in the condition you need with 'or' condition.

I used it to search items to add to a cage but wanted to show the user only items that are not already in my cage (and also not assigned to any other cage), so in my controller:

   @search = Cage.ransack(params[:q])
   @search.build_grouping({:m => 'or', :storage_id_not_eq => @cage_item.id, :storage_id_null => true})  
   @results_count = @search.result(:distinct => true).length
   @items = @search.result(:distinct => true).paginate(:per_page => 20, :page => params[:page] || 1)

   @search.build_condition if @search.conditions.empty?
   @search.build_sort if @search.sorts.empty?

So when I'm printing the query ransack is running it will show:

 @search.result.to_sql
"SELECT `specimens`.* FROM `specimens`  WHERE (`specimens`.`deleted_at` IS NULL) AND ((`specimens`.`storage_id` != 183 OR `specimens`.`storage_id` IS NULL))"

and if I add a name query:

@search.result.to_sql
"SELECT `specimens`.* FROM `specimens`  WHERE (`specimens`.`deleted_at` IS NULL) AND ((`specimens`.`name` LIKE '%root%' AND (`specimens`.`storage_id` != 183 OR `specimens`.`storage_id` IS NULL)))"

Hope this helps you or someone else with having trouble using OR in ransack conditions instead of the basic AND.

这篇关于Rails gem Ransack ->使用“is null"和特定值搜索“or condition"(field=x OR field IS NULL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！