Rails custom model method in where query

Question

In my rails app I have defined in the Kid model a calculation based on the fields from the Kids DB. the method is as follows:

def flip_date 
  self.dob.advance(months: 10) 
end

I want to use this in my controller as I have a method where I am defining something as follows:

new_kids = Kid.where(discharge_date: nil).where('flip_date > ?', Date.current.advance(year: 1).beginning_of_year)

However I keep getting the following error:

SQLite3::SQLException: no such column: flip_date: SELECT "kids".* FROM "kids" WHERE "kids"."discharge_date" IS NULL AND (flip_date < '2017-01-01')

Any ideas on how can I make this work? All help is appreciated!

I think you will have to use `scope` see the syntax and examples in the [documentation](http://api.rubyonrails.org/classes/ActiveRecord/Scoping/Named/ClassMethods.html#method-i-scope) — Imran Ali, Sep 11 '16 at 05:16
@ImranAli I looked through the documentation but I still do not think it will solve the problem, as there is still a query happening the `scope` definitions but `flip_date` is not a column — Alvin Lau, Sep 11 '16 at 05:23
@AlvinLau what are you trying to achieve through where query? where method will create SQL statement, you will not be able to use model methods in sql. — dnsh, Sep 11 '16 at 05:27

dnsh · Accepted Answer · 2016-09-11T05:41:26.283

3

If you really want to use model methods take a look at http://apidock.com/rails/v4.0.2/ActiveRecord/QueryMethods/select

For your case:

new_kids = Kid.where(discharge_date: nil).select{|k| k.flip_date > Date.current.advance(year: 1).beginning_of_year}

But select method takes every object in memory before returning final result. Hence I will advise to use normal where clause and instead of flip_date take dob (which is a column in database) in consideration.

Like this

new_kids = Kid.where(discharge_date: nil).where('dob > ?', <date criteria>)

edited Sep 11 '16 at 05:41

answered Sep 11 '16 at 05:30

dnsh

3,516
2
22
47

Exactly what I was looking for, the select command. Thank you! – Alvin Lau Sep 11 '16 at 09:10
1

select returns Array. How can I get the ActiveRecord_Relation instead of Array? – hobbydev May 10 '19 at 04:15
@hobbydev I am looking for the same. Did you find anything on "How can I get the ActiveRecord_Relation instead of Array?" ? – Anas Ansari Jun 20 '21 at 18:20

score 0 · Answer 2 · answered Nov 22 '21 at 11:07

The select method (http://apidock.com/rails/v4.0.2/ActiveRecord/QueryMethods/select) works great if you are okay with the return being an Array.

I am still looking for a way to do this with an ActiveRecord_Relation return.

If others know how to do this, it would be much appreciated if you can share.

score 0 · Answer 3 · answered May 18 '22 at 20:28

This example doesn't respond to your specific code, but to the extent it helps someone else with a similar question, here's a very simple example of how .select can be really handy:

@expired_memberships = User.select{|u| u.membership_expired_yesterday?}

In that example you've looped through all your Users and filtered them based on a custom method you defined on the User model (membership_expired_yesterday?). Now you can easily do stuff with that collection like this example in a mailer:

@expirations.each do |user|
  MembershipExpirationMailer.with(user: user).first_reminder.deliver_now
end

Rails custom model method in where query

3 Answers3

Linked