0

Here's some context - let's say I have the following relationships:

class Session < ActiveRecord::Base
    has_many     :events
end

class Event < ActiveRecord::Base
    belongs_to   :session
    has_many     :event_attributes
end

class EventAttribute< ActiveRecord::Base
    belongs_to   :event
end

And here is the migration for the event attributes table:

create_table  :event_attributes do |t|
    t.string  :key
    t.string  :value
    t.integer :event_id
end

Now the problem - If I have all the events that belong to a session, like so:

session = Session.find(1)
events = session.events

How do I find events that have:

An event_attribute where the key column is 'name' and the value column is 'Bill'

AND

An event_attribute where the key column is 'city' and the value column is 'Seattle'

I tried something like this:

events.includes(:event_attributes)
.where(event_attributes: { key: 'name', value: 'Bill'    })
.where(event_attributes: { key: 'city', value: 'Seattle' })

This provides 0 results. If I query a single where condition I get the expected result, I'm just not sure how to search on a collection with multiple conditions on the has_many association.

Porkcrop
  • 77
  • 1
  • 8
  • this question may help you http://stackoverflow.com/questions/15523627/rails-eager-loading-and-where-clause – Guru Mar 30 '16 at 06:47
  • I believe that question provides information about querying through a has_many association on a singular active record object with a single condition. I'm trying to search on a collection through a has_many association with multiple conditions on the same columns. If I missed something, let me know - thanks for the link though, appreciate it. – Porkcrop Mar 30 '16 at 07:04

2 Answers2

2

You need to use an a AND b OR c AND d in the the where clause. Rails 4 does not have built in support for OR so you would need to use a SQL string.

sql = <<-EOS
  event_attributes.key = 'name' AND event_attributes.value = 'Bill'
  OR
  event_attributes.key = 'city' AND event_attributes.value = 'Seattle'
EOS

events = Event.joins(:session, :event_attributes)
      .where(session: { id: 1 })
      .where(sql)
      .group('event_attributes.id')
      .having("count(*) = ?", 2)

If you need to construct the query dynamically you may want to look into the Arel API (which is what ActiveRecord uses to construct SQL).

Although I would personally take the simple road and just add columns to the event table instead since key/val tables are a real PITA.

max
  • 96,212
  • 14
  • 104
  • 165
  • I haven't played around enough with the Rails 5 `.or` method to know if you can hook it up with an `AND` and construct the above query. – max Mar 30 '16 at 07:28
  • Should've specified I'm using Rails 4. I'll check this out @max and let you know the results. And unfortunately, the key/val table has to stay for now. I simplified the search case for ease of communication. – Porkcrop Mar 30 '16 at 07:31
  • Appears we are almost there @max. Currently the having clause at the end causes it to return 0 results. Since that's what is enforcing that the event has both event_attributes then without it you get ambiguous results.I've verified thoroughly that the event meets both conditions (both at the database level and by doing single event_attribute condition queries). – Porkcrop Mar 30 '16 at 08:27
  • Thinking we need to group on events.id instead of event_attributes.id – Porkcrop Mar 30 '16 at 08:41
  • I adapted this from the [answer to one of my own questions](http://stackoverflow.com/questions/36131803/sql-where-joined-set-must-contain-all-values-but-may-contain-more). I'm a bit unsure about why it returns an empty set and can't setup an app to test it right now. I would try asking the same thing it as a pure SQL question - they tend to get better answers on SO as there are a lot more truly skilled people following the `postgres` or `mysql` tags than rails. – max Mar 30 '16 at 09:18
  • You might also need some kind of multicolumn uniqueness index on `event_id, key, value`. Something like this: `add_index :events_attributes, [:event_id, :key, :value], unique: true`. – max Mar 30 '16 at 09:30
  • 1
    I think your initial answer was correct minus the grouping. I have it grouping on event.id on the event_attributes instead of event_attribute.id. So now the 'having' clause returns true if it found the number of event_attributes to be equal to the number of conditions (so 2 in the example). – Porkcrop Mar 30 '16 at 09:42
0

Actually, you are applying where on where thats why its not returning expected results. In first where clause you are getting 1 record and applying second where clause on first one is resulting in 0 records.

Event.includes(:event_attributes).where("(event_attributes.key = ? AND event_attributes.value = ?) OR (event_attributes.key = ? AND event_attributes.value = ?)", "name", "Bill", "city", "Seattle")

will return the results you need.

Muhammad Ali
  • 2,173
  • 15
  • 20
  • I was thinking along the same lines but won't this return ambiguous matches? It would match `{ key: 'name', value: 'Seattle' }` for example. It also does not enforce that the event should have both event_attributes. – max Mar 30 '16 at 07:11
  • @Muhammad Yawar Ali Being that you're using 'IN', wouldn't that give me events where it has: An event_attribute that has a key column equal to 'name' and a value column equal to 'Bill' OR An event_attribute that has a key column equal to 'city' and a value column equal to 'Seattle' I only want events that have both those event_attributes. I could be mistaken. --- EDIT: max said what I was trying to say ---- – Porkcrop Mar 30 '16 at 07:16
  • I have editted the answer try it now, it should return results as you require. – Muhammad Ali Mar 30 '16 at 07:25