search only year in a date input field. about ransack

[arjes]

Like I said in the rubyonrails-talk group...

Make a custom predicate, use extract if your specific DB supports it, or use the built in lt and gt comparators to check if it falls within the year's boundaries. This is not a needed predicate.

On 19 October 2011 17:51, arjes
[email protected]
wrote:

Like I said in the rubyonrails-talk group...

Make a custom predicate, use extract if your specific DB supports it, or use the built in lt and gt comparators to check if it falls within the year's boundaries. This is not a needed predicate.

Can you suggest an example?

[arjes]

Better yet, use a scope.

scope :during, lambda { |y|
where('date_field > ? and date_field < ?', DateTime.parse("#{y}-01-01")-1.second, DateTime.parse("#{y+1}-01-01"))
}

[lunks]

Trying not to create an issue to a similar but not quite the same thing, I'm curious on how to integrate a simple range for searching objects that has a default, but at the same time can get Ransack options. I have a scope on the model and currently I'm scoping the search on the controller, but rather would like to use in Ransack search itself.

Could you point me to an example or how to define this kind of thing? It doesn't look difficult on meta_search, but I couldn't find any relevant documentation about this on Ransack code.

[ChristopheBelpaire]

I tried to do something like that (a predicate date_equals to compare date),
but as my field in the database is a timestamp, it does'nt work.
Is it possible to cast the field in date ?

Ransack.configure do |config|
  config.add_predicate 'date_equals', # Name your predicate
                       # What non-compound ARel predicate will it use? (eq, matches, etc)
                       :arel_predicate => 'eq',
                       # Format incoming values as you see fit. (Default: Don't do formatting)
                       :formatter => proc {|v| "date(#{v.to_date})"},
                       # Validate a value. An "invalid" value won't be used in a search.
                       # Below is default.
                       :validator => proc {|v| v.present?},
                       # Should compounds be created? Will use the compound (any/all) version
                       # of the arel_predicate to create a corresponding any/all version of
                       # your predicate. (Default: true)
                       :compounds => true,
                       # Force a specific column type for type-casting of supplied values.
                       # (Default: use type from DB column)
                       :type => 'date'
end

[arjes]

@ChristopheBelpaire equals works. Ransack shouldn't be dealing with type conversion, it just passes that up the chain. As such, .search(:created_at_eq => Datetime.now) should work. If it doesn't open a new ticket, it should.

@lunks, your right it was very easy in meta_search. I have been reading the ransack code to try to figure it all out but I'm still catching up. ATM I can't answer to how to do this in Ransack since I have not figured it out yet. I will respond if I find something.

[ChristopheBelpaire]

@arjes my problem is that in my database the field is a datetime, so the equals compare date and time and I want only the date, I managed to avoid this with :

Ransack.configure do |config|
  config.add_predicate 'date_equals', # Name your predicate
                       # What non-compound ARel predicate will it use? (eq, matches, etc)
                       :arel_predicate => 'eq',
                       # Format incoming values as you see fit. (Default: Don't do formatting)
                       :formatter => proc {|v| Arel::Nodes::NamedFunction.new("date",v.to_date.to_s(:db)) },
                       # Validate a value. An "invalid" value won't be used in a search.
                       # Below is default.
                       :validator => proc {|v| v.present?},
                       # Should compounds be created? Will use the compound (any/all) version
                       # of the arel_predicate to create a corresponding any/all version of
                       # your predicate. (Default: true)
                       :compounds => true,
                       # Force a specific column type for type-casting of supplied values.
                       # (Default: use type from DB column)
                       :type => :date
end

and in my model :

  ransacker :created_at_casted do |parent|
    Arel::Nodes::NamedFunction.new("date", Arel::Nodes::SqlLiteral.new('orders.created_at'))
  end

I don't know if it's the cleanest way but it works ...

[arjes]

@ChristopheBelpaire, Ah i missed that in your first post. Your method looks as good as I can figure out at the moment.

[carvil]

@ChristopheBelpaire could you please let me know which version of rails/ransack/arel are you using with your solution? I wanted to do exactly the same (need to filter by date and ignore time), but I get this error:

undefined method `map' for "articles.created_at":Arel::Nodes::SqlLiteral

In my model I have:

ransacker :created_at_casted do |parent|
  Arel::Nodes::NamedFunction.new("date", Arel::Nodes::SqlLiteral.new('articles.created_at'))
end

My initialiser is exactly like yours and finally, in my view:

<%= search_form_for @q, html: {class: "pull-right form-search article_search"} do |f| %>
   . . .
  <%= f.text_field :created_at_casted_date_equals, placeholder: "Date" %>
   . . .
  <%= f.submit "Search", class: "btn" %>
<% end %>

The full stack trace: https://gist.github.com/2000742

[carvil]

I ended up solving the problem with this in the model:

ransacker :created_at_casted do |parent|
  Arel::Nodes::SqlLiteral.new("date(articles.created_at)")
end

The following custom predicate:

Ransack.configure do |config|
  config.add_predicate 'date_equals',
    :arel_predicate => 'eq',
    :formatter => proc {|v| v.to_date.to_s(:db) },
    :validator => proc {|v| v.present?},
    :compounds => true,
    :type => :date
end

And the search form above.

[Epiju]

Hi,

Like carvil I have in my model a datetime for created_at although I wanted the "equals" predicate to compare the created_at and a date (like '2012-09-26').

So I added in my model (in order to add casted attributes and take off the old created_at/update_at/deleted_at :

ransacker :created_at do
    Arel::Nodes::SqlLiteral.new("date(items.created_at)")
  end

  ransacker :updated_at do
    Arel::Nodes::SqlLiteral.new("date(items.updated_at)")
  end

  ransacker :deleted_at do
    Arel::Nodes::SqlLiteral.new("date(items.deleted_at)")
  end

  # Hide some attributes for advanced search
  UNRANSACKABLE_ATTRIBUTES = ["created_at", "updated_at", "deleted_at"]

  def self.ransackable_attributes auth_object = nil
    (column_names - UNRANSACKABLE_ATTRIBUTES) + _ransackers.keys
  end

But when I confirm the query (created_at equals to '2012-03-24') I have this error:

NoMethodError (undefined method `name' for "date(items.created_at)":Arel::Nodes::SqlLiteral):

Surprisingly, it works with "greater than" and "less than". Only "equals" occurs this error.

I made all of this for all my models and 60% works (the remain 40% occurs this error).

In the console :

irb(main):232:0> Item.search(:created_at_eq => Date.today.to_s).result
(Object doesn't support #inspect)

Thanks for your help

[ak47]

@Epiju Hi, did you ever root this issue?

NoMethodError (undefined method `name' for "date(items.created_at)":Arel::Nodes::SqlLiteral):

I'm getting the same issue, but it appears to be related to adding #page from Kaminari.

as in...

@search.result.page

Wondering if #name needs to be supplied in the ransacker defined or predicate, though neither has worked out yet.

[link-er]

Hello
I used your solution, but I got strange error - when I use ransack dynamic form and select second attribute for filtration after the date, somehow, only first number of date is taken and I have an error

PG::Error: ERROR:  operator does not exist: text = integer
LINE 1: ...WHERE ((to_char(tickets.created_at, 'DD-MM-YYYY') = 13 AND "

Although parameters were right

Parameters: {"utf8"=>"✓", "authenticity_token"=>"Idl1L0BzBPikpkq2ne7vQnuueAYw+ygmiBR20iESte4=", "q"=>{"c"=>{"0"=>{"a"=>{"0"=>{"name"=>"created_at"}}, "p"=>"eq", "v"=>{"0"=>{"value"=>"13-06-2013"}}}, "1371131726147"=>{"a"=>{"0"=>{"name"=>"user_username"}}, "p"=>"eq", "v"=>{"0"=>{"value"=>"root"}}}}}, "commit"=>"Поиск"}

P.S. Most strange thing is that when parameter with date comes second everything is ok

[bendilley]

I borrowed @Epiju's ransacker and found that telling it the type avoided the 'operator does not exist: text = integer' error.

ransacker :created_at_date, type: :date do |parent|
  Arel.sql('date(items.created_at)')
end

[bonyiii]

The wiki now has an example that resembles to this problem example 2

And here is how i do date only search in a time field it:

In view i have this:

    f.text_field :version_created_at_gteq
    f.text_field :version_created_at_lteq

in the model this:

   ransacker :created_at, type: :date do
     Arel.sql('date(versions.created_at)')
   end

My motivation was to display only date and not the full time data in the search form.

[jonatack]

@bonyiii thanks, which example is it?

I see a new one (example number 7) that could use better documentation concerning what the attributes are and the relation between them. I cleaned the example up a bit to use #pluck instead of the verbose query + map but it's not very clear to me what the attributes are and how they relate.