Rails 3.2 - ActiveRecord `where` query with method called on attribute

Question

In the console I can query my Job table like this: jobs = Job.where("created_at <= ?", Time.now)

I want to get jobs that were created this year. If I get an individual job I can use job.created_at.year but if I try to use jobs = Job.where("created_at.year = ?", 2014) I get the following error:

SQLite3::SQLException: no such column: created_at.year

I think I understand that the problem is I'm trying to mix Ruby into a SQL query but I'm not sure how to get around it. Understanding this would help me create much more powerful ActiveRecord queries.

EDIT I found that I can do this using .map like so: Job.all.map { |j| j if j.created_at.year == 2014 }. Is that the best way to do this if I want to gather a collection of jobs that requires calling a method on an attribute?

Surya · Accepted Answer · 2014-11-05 20:02:56Z

3

Try this:

Job.where(:created_at => Time.now.beginning_of_year..Time.now.end_of_year)

Since, calling Time.now could potentially give two different years (before and after midnight on December 31) it would be better to first create a variable for the current time before it is used in a query:

current_time = Time.now
Job.where(:created_at => current_time.beginning_of_year..current_time.end_of_year)

edited Nov 5, 2014 at 20:02

answered Nov 5, 2014 at 18:39

Surya

16k3 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Michael Berkowski Over a year ago

Or much more simply using MySQL's native YEAR() function: "YEAR(created_at) = ?" which will accept Time.now.year

Surya Over a year ago

@MichaelBerkowski : Oh, interesting. Thanks for your inputs. :)

mu is too short Over a year ago

@MichaelBerkowski Except that time and date handling functions tend to be database specific and ActiveRecord is too dumb to cover this portability problem; note the SQLite exception in the question. Of course, trying to achieve database portability through an ORM is generally a waste of time unless you're going to do it the hard way (i.e. yourself) or talk to the database in baby-talk-SQL so this is rarely a real problem.

Surya Over a year ago

@muistooshort : After reading your comment I too feel that how ORM like ActiveRecord have poor/no-implementation of methods to produce complex queries like this. Hell, even NOT got introduced in Rails 4 and was wondering what took them so long for making a method to produce such a useful query. ActiveRecord does provide db portability but it's good for making only normal where and join queries, otherwise one has to write raw SQLs to get the job done in real life scenarios, which again kills the benefit of having an ORM in the first place. You've put thoughts very well. Thank you.

sixty4bit Over a year ago

This works for my example case but I'm still wondering about the more general question of how to call methods on attributes within a .where query. Another example would be if I need to make time comparisons using a time attribute that is stored as a string. For example, I can't do Job.where("string_time.to_time > ?", Time.now)

|

Collectives™ on Stack Overflow

Rails 3.2 - ActiveRecord `where` query with method called on attribute

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related