I have a rails schema and I have users table and postings table. I am trying to get all users that either have no postings or no active postings. Active being a column on postings that can be true or false. Is the following correct?
User.joins('LEFT JOIN postings on users WHERE postings.user_id = users.id').where('postings.id = NULL').or(where('postings.active = false')).
The thing is, if the user has any postings that are active: true, I DO NOT want them as they have at least one active posting. I am currently getting back users that have active: false posts but also active:true posts.
Advertisement
Answer
i think you no need to filter on left_join, let it be then we can group by user and sum all postings status, if the sum is zero that mean that user does not contains any active postings.
User.left_joins(:postings)
.group("users.id")
.having("postings.active IS NULL OR SUM(CAST(postings.active AS TINYINT)) = 0")
# having("SUM(case when postings.active IS NULL OR postings.active = false then 0 else 1 end) = 0")