ActiveRecordy has_many through I need to find all parents where at least one child meet the condition

Question

I have three activerecord models:

the "A"

class User < ApplicationRecord
  has_many :user_violation, class_name: "UserViolation", inverse_of: :user
  has_many :violations, through: :user_violation, class_name: "Violation"
end

the middle:

class UserViolation < ApplicationRecord
  belongs_to :user, class_name: "User", foreign_key: :user_id
  belongs_to :violation, class_name: "Violation", foreign_key: :violation_id
end

the B:

class Violation < ApplicationRecord
end

I need to find all users who have AT LEAST one violation with column: fatal set to true.

Im kinda stuck here and this is not working:

User.joins(:violations).where('violations.fatal = true')

Is 'User.joins(:violation).where(violations: {fatal: 'true'})' working for you? I try to duplicate the issue to myself strangely violations relation on user is behaving like a belongs_to relation — Semih Arslanoglu, Aug 03 '20 at 09:39

score 3 · Accepted Answer · answered Aug 03 '20 at 08:37

3

To using query filter conditon in ORM I think this syntax should be:

User.joins(:violations).where(violations: {fatal: 'true'})

answered Aug 03 '20 at 08:37

Ninh Le

1,291
7
13

What is the error you encounter when running this command? – Ninh Le Aug 03 '20 at 10:17
It’s just an empty record set – Giova.panasiti Aug 03 '20 at 10:27
Is that result is wrong? Can you show me the plain where above by adding ‘.to_sql’ at the end of the query. – Ninh Le Aug 03 '20 at 10:35
Maybe you should try: `User.joins(violations: :user_violation).where(violations: {fatal: 'true'})` – Ninh Le Aug 03 '20 at 10:46
Where there are multiple violations, this will return multiple User instances. 100,000 violations means you get the User 100,000 times. – David Aldridge Aug 15 '20 at 21:17

score 1 · Answer 2 · answered Aug 03 '20 at 17:02

I think @Ninh Le's answer is right,(so did yours!) maybe you can simplify your models' code first to try to find where the problem is. For example, I notice you missed the 's' in the has_many relation:

class User < ApplicationRecord
  has_many :user_violations
  has_many :violations, through: :user_violations
end

class UserViolation < ApplicationRecord
  belongs_to :user
  belongs_to :violation
end

class Violation < ApplicationRecord
  # has_many :user_violations
  # has_many :violations, through: :user_violations
end

I think it should work, if it's still not, check if there is record that has violation.fatal == true.

Then add the options of has_many, belongs_to if there 'really' needed.

score 0 · Answer 3 · answered Aug 15 '20 at 15:52

For the most efficient approach to finding these users, you'd want what the database people would call a "semi-join" - an exists subquery.

Assuming that "fatal" is defined by a fatal column having a value of true

User.
  where(
  Violation.
    where(fatal: true).
    joins(:user_violations).
    where(
      UserViolation.arel_table[:user_id].eq(User.arel_table[:id])
    ).arel.exists
  )

I'd suggest adding a scope to Violation that defines fatal without needing a where clause in that.

ActiveRecordy has_many through I need to find all parents where at least one child meet the condition

3 Answers3