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.
OK - here is my own solution - inspired by graywh (thank you very much): ransack/issues/290
You can add a new grouping node -with an 'or'-combinator- to the ransack object.
In index view I changed f.select :user_id_eq
to select_tag ...
tickets_controller.index:
@q = Ticket.search(params[:q])
if params[:user_id_eq] && params[:user_id_eq]!=''
@q.build_grouping({:m => 'or', :user_id_eq => params[:user_id_eq], :user_id_null => true})
end
You could build your query params like this
q: {g: [ {m: 'or', user_id_eq: 5, user_id_null: 1} ] }
where this:
user_id_null: 1
is only flag to search for null value