Is it possible to call model method in where clause?

Question

I have two models

class Project
   has_one: user
end

class User
   # Attributes
   # active: Boolean
   # under_18: Boolean

   def can_work?
     active? && under_18 == false
   end
end

The logic for can_work?

if active is true and under_18 is false then they can work

I want to do something like this but it's not possible

Project.all.joins(:user).where('users.can_work? = ?', false)

Essentially what I'm looking for is to find all users who can't work

I know I can use Scope, but copying the logic that I specified above in scope is confusing.

Here's the scenario that I'm looking for

active |  under_18
------------------
   T        T        = F
   T        F        = T
   F        T        = F
   F        F        = F

Thanks

You can't use model methods like that, no. Only database attributes. — Sergio Tulentsev, May 21 '21 at 13:06
You can create a scope for this and it does not seem confusing to me. `scope can_work, -> {where(active: true, under_18: false)}` assuming those are the columns. If you just store age then `scope can_work, -> {where(active: true).where(arel_attribute(:age).gteq(18))}` will do the trick — engineersmnky, May 21 '21 at 13:16
@engineersmnky Using scope is possible - `scope can_work, -> {where(active: true, under_18: false)}`, How do I negate `can_work`? if you look at the table above negating `can_work` can be confusing but that's the requirement — airsoftFreak, May 21 '21 at 13:20
@engineersmnky hence why i decided to go for this route `where('users.can_work? = ?', false)` but according to Sergio it is not possible :( — airsoftFreak, May 21 '21 at 13:21
@airsoftFreak: try this then: `User.where.not(User.can_work.where_values_hash)` — Sergio Tulentsev, May 21 '21 at 13:30
Or you could inline the scope to gain readability if you don't need it elsewhere: `User.where.not(active: true, under_18: false)` — Sergio Tulentsev, May 21 '21 at 13:31
@SergioTulentsev thanks for the answer, unfortunately `User.where.not(active: true, under_18: false)` will just negate the `active` and `under_18` I'm trying to achieve what the table above is doing. — airsoftFreak, May 21 '21 at 13:38
Example if `active: true and under_18: false -----> active: false and under_18: true` — airsoftFreak, May 21 '21 at 13:38
I see. The AND should become an OR in the negated expression. Well, you can always write it out explicitly. `scope :cant_work, -> { where("not active or under_18")}` or a variant of this (using arel_table/arel_attribute, for example) — Sergio Tulentsev, May 21 '21 at 13:48
Thanks but `scope :cant_work, -> { where("not active or under_18")}` this logic still is not helping. I'm not sure how to do the exact logic as in the table above. Appreciate your help anyways. — airsoftFreak, May 21 '21 at 14:13
Seems simple enough to go with `User.where.not(id: User.can_work.select(:id))` — engineersmnky, May 21 '21 at 14:17
@airsoftFreak: wait a second, that truthiness table describes "can_work", not the "cant_work". — Sergio Tulentsev, May 21 '21 at 16:56
Untested and just writing by hand but `scope :can_work, ->(t=true) {where(Arel::Grouping.new(arel_attribute(:active).eq(true).and(arel_attribute(:under_18).eq(false))).eq(t))}` should work. This will allow for `User.can_work` for users that can work and `User.can_work(false)` for users that can't work. — engineersmnky, May 22 '21 at 04:22
@SergioTulentsev After further testing, I can confirm that your code snippet was working on my side. I'm really grateful for your time to help me with this. `scope :cant_work, -> { where("not active or under_18")}` — airsoftFreak, May 22 '21 at 12:00
@engineersmnky Your code snippet is working as well, I'm really grateful for your help as well. I used a combination of your code and Sergio to prevent ambiguous error if I just do it this way `.merge(User.cant_work)` instead I use your snippet `.merge(User.where(id: User.cant_work.select(:id)))` — airsoftFreak, May 22 '21 at 12:03
@engineersmnky I haven't tested this code yet `scope :can_work, ->(t=true) {where(Arel::Grouping.new(arel_attribute(:active).eq(true).and(arel_attribute(:under_18).eq(false))).eq(t))}`. — airsoftFreak, May 22 '21 at 12:04
Thanks a lot for both of your help, you guys really help me, really appreciate it. i will create a bounty tomorrow and split the points since both of you did help me. Thanks guys. The final code that I use is `.merge(User.where(id: User.cant_work.select(:id)))` — airsoftFreak, May 22 '21 at 12:07
@airsoftFreak: note that this last version performs two DB queries and is potentially slow (if `cant_work` returns thousands or tens of thousands users) — Sergio Tulentsev, May 24 '21 at 11:18
@SergioTulentsev if you are referring to `User.where(id: User.cant_work.select(:id))` this will only preform 1 query technically speaking because the where will be converted to "NOT IN( sub query)" which is far more performant than "NOT IN(list of ids)" but yes a true inversion of conditions will be more performant but string SQL makes it more difficult to recognize and change in the future. Overall the sub query will likely result in very reasonable response times since id is a primary key and unique indexed by default. — engineersmnky, May 26 '21 at 22:16
@engineersmnky: ah, I thought that `select` was a `pluck`. My bad. You're right, of course. — Sergio Tulentsev, May 27 '21 at 10:58

score 4 · Answer 1 · answered Jun 04 '21 at 17:55

4

As per Sergio Tulentsev's answer, you can't do it. But if you want it DRY so badly you can use scopes.

Scope definition alt. 1:

scope :can_work, -> { where active: true, under_18: false }
scope :cant_work, -> { where.not id: User.can_work.pluck(:id) }

Scope definition alt. 2:

scope :can_work, ->(t=true) { where(active: t).where.not(under_18: t) }

New can_work? method

def can_work?
  User.can_work.pluck(:id).include?(id)
end

Now you can call:

Project.joins(:user).merge(User.cant_work)

Or

Project.joins(:user).merge(User.can_work(false))

PS. Good luck with the speed.

answered Jun 04 '21 at 17:55

blackbiron

809
10
17

I agree with this, convention as opposed to configuration. – wamae Jun 06 '21 at 15:04
using a scope is the best way available and recommended. – Sagar Kamble Jun 09 '21 at 05:55

H. Almidan · Answer 2 · 2021-06-06T15:05:29.363

Try

Project.joins(:users).where(user: {under_18: <Bool>, active: <Bool>})

Insert the Boolean values (true or false) that you want in place of <Bool>s above.

Source

Side note

If users are workers then I suggest changing names in your schema to look like:

workers

id	project_id	under_18	active
1	1	TRUE	TRUE
...	...	...	...

projects

id	title
1	"lorem"
...	...

So the associations would be

class Project
   has_one :worker
end

class Worker
   belongs_to :project
end

And the query would then look like

Project.joins(:workers).where(worker: {under_18: <Bool>, active: <Bool>})

I think it makes more sense from a naming standpoint.

That is since users are oftentimes regarded as the owners in association relationships (i.e. a project would belong to a user, and a user would have many projects). Just a quick side suggestion.

Sagar Kamble · Answer 3 · 2021-06-09T10:11:37.227

Answer: Scope

Scopes are custom queries defined in Rails model inside method named scope

A scope can have two parameters, the name of the scope and lambda which contains the query.

Something like:

class User < ApplicationRecord

  scope :is_active, -> { where(active: true) }

  scope :under_18, -> { where(under_18: true) }

end

Scope returns ActiveRecord::Relation object. So scopes can be chained with multiple scopes like,

User.is_active.under_18

The scope is nothing but a method same as class methods. The only difference is that you get a guarantee of getting ActiveRecord::Relation as an output of scope. This helps you to write specific code and helps to reduce errors in the code.

Default Scope: You can also add a default scope on any model which is implicitly applied to every query made on the respective model. Be cautious while using this though as this might cause unpredictable results.

For example:

class User < ApplicationRecord

  default_scope :is_active, -> { where(active: true) }

  scope :under_18, -> { where(under_18: true) }

end

In the above case, User.first will return the first user who is active.

Benefits of Scope

Testable - The separate scopes are more testable now as they now follow the Single Responsibility Principle
Readable
DRY - Combining multiple scopes allows you not to repeat the code

Is it possible to call model method in where clause?

3 Answers3

Side note