CODEWITHSUNDEEP
ruby-on-railsactiverecord
Kiet Tran
Kiet Tran

Reputation: 1538

How to stop ActiveRecord from combining queries?

Stripping out the irrelevant parts, I have the following models:

class Member < ActiveRecord::Base
  attr_accessible :custom_fields_attributes
  has_many :custom_fields, :as => :custom_fieldable
end

class CustomField < ActiveRecord::Base
  attr_accessible :key_id, :value
  belongs_to :key
  belongs_to :custom_fieldable, :polymorphic => true
end

class Key < ActiveRecord::Base
  attr_accessible :key
end

Suppose I have two entries in my keys table:

---------------
id | key
---+-----------
1  | Hair color
2  | Height

how do I run a query that would let me retrieve all members with "Brown" hair color and "5ft" in height?

I tried doing it as two successive commands:

ms = Member.joins(:custom_fields).where("custom_fields.key_id = 1 AND custom_fields.value = 'Brown'")
ms.joins(:custom_fields).where("custom_fields.key_id = 2 AND custom_fields.value = '5ft'")

However, this doesn't work because ActiveRecord runs the second query like this:

SELECT `members`.* FROM `members` INNER JOIN `custom_fields` ON
`custom_fields`.`custom_fieldable_id` = `members`.`id` AND
`custom_fields`.`custom_fieldable_type` = 'Member' WHERE
(custom_fields.key_id = 1 AND custom_fields.value = 'Brown') AND
(custom_fields.key_id = 2 AND custom_fields.value = '5ft')

The query above returns an empty record because no custom field can be simultaneously two things at once.

What I would like to do is to have Rails evaluate the first result, and then run the second query on it. How would I do that?

The version I'm using is Rails 3.2.14.

Upvotes: 0

Views: 144

Answers (1)

sevenseacat
sevenseacat

Reputation: 25029

I suspect you will have to alias one (or both) of the times you're joining the custom_fields table if you want to do it in one query.

Something like:

Member
 .joins('custom_fields as custom_fields_1').where("custom_fields_1.key_id = 1 AND custom_fields_1.value = 'Brown'")
 .joins('custom_fields as custom_fields_2').where("custom_fields_2.key_id = 2 AND custom_fields_2.value = '5ft'")

Alternatively, run the two queries separately, and merge the results. eg.

brown_members = Member.joins(:custom_fields).where("custom_fields.key_id = 1 AND custom_fields.value = 'Brown'")
height_members = Member.joins(:custom_fields).where("custom_fields.key_id = 2 AND custom_fields.value = '5ft'")
ms = brown_members.merge(height_members)

Upvotes: 1

Related Questions