Find all records that contain one join model but not another using active record

Question

I have the following schema in my rails app.

class Campaign < ApplicationRecord
  has_many :businesses
  has_many :clients, through: :businesses
end

class Business < ApplicationRecord
  has_many :clients
  belongs_to :campaign
end

class Client < ApplicationRecord
  has_many :events
  belongs_to :business
end

class Event < ApplicationRecord
  belongs_to :client

  enum category: {
    :processed,
    :delivered,
    :opened
  }
end

I need a way of finding all clients in a campaign that have a processed and delivered event category but not a opened category

My naive way of doing this was:

c = Campaign.first
c.clients.joins(:events).where('events.category' => [0, 1]).where.not('events.category' => [2])

But this won't work.

This query will run across some pretty big tables so eager loading isn't an option.

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

Try these as enums cannot be normally compared like basic strings.

c = Campaign.first
c.clients.joins(:events).where.not("events.category = (?)", Event.category[:opened] )

or just

c = Campaign.first
c.clients.joins(:events).where("events.category = (?)",[ Event.category[:processed], Event.category[:delivered]] )

More Info can be find here

You can also see https://stackoverflow.com/a/29135693/7046734

edited May 23 '17 at 12:32

Community

1
1

answered Apr 18 '17 at 14:55

Mayank

727
6
9

Thanks but that still won't help here. I'm already using the raw enum value in the query, the problem is still the same. – KJF Apr 18 '17 at 15:27

Find all records that contain one join model but not another using active record

1 Answers1