CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlactiverecord
brainburn
brainburn

Reputation: 55

ActiveRecord query to avoid needing of additional processing of the results

I have two models: Employment and Email. Employment has_many :emails and Email belongs_to :employment.

I need to find employments that have no working emails.

This means I need to find employments with associated emails where deactivated_on is not nil and also have no associated emails where deactivated_on is nil. (deactivated_on is a date)

I could find all of the employments with emails where deactivated_on is nil and then iterate through each employment's emails in ruby to return only the employments with no emails where deactivated_on is nil. But I expect there must be a way to write a query that achieves this result.

I am open to ActiveRecord or raw sql if necessary.

I currently have:

class Employment < ActiveRecord::Base
  has_many :emails

  def no_working_emails
    joins(:emails).where.not(emails: { deactivated_on: nil }).uniq
  end
end

This results in employment objects associated with emails where deactivated_on is nil and also emails where deactivated_on is not nil.

I need to remove the employments that also have emails where deactivated_on is not nil.

I have been researching group with having or a subquery using from. But so far I can't shake off those employments with active email addresses.

Upvotes: 2

Views: 55

Answers (1)

jbranchaud
jbranchaud

Reputation: 6087

A query similar to the following should help you get at the IDs of all employments that have no active emails.

ActiveRecord::Base.connection.select_all <<-SQL
  select e.id
  from employments e
  where not exists (
    select 1
    from emails em
    where em.employment_id=e.id
      and em.deactivated_on = null
  );
SQL

Upvotes: 1

Related Questions