0

I have 3 models:

Tiersets
  has_many Tiers

Tiers
  belongs_to Tierset
  has_and_belongs_to_many Features

Feature
  has_and_belongs_to_many Tiers

In Feature, I have a String column named Feature_Code that contains one of the following strings: "F_VIZ", "F_DATA", "F_SCORE".

I'm trying to construct a query to find, within a known Tierset, all the Feature objects with code F_VIZ in all Tier objects for that Tierset.

I've tried a bunch of combinations of :includes in AREL queries, but I'm clearly getting mixed up with how the tables are joined. Any help is much appreciated.

tarunsachdeva
  • 453
  • 1
  • 4
  • 13

1 Answers1

1

The SQL you want is

select * from features, features_tiers, tiers where features_tiers.id = features.id and features_tiers.tier_id = tiers.id and features.code = 'F_VIZ' AND tierset_id = ?;

so we'll translate this directly to ActiveRecord:

Feature.joins(:tiers).where(:code => 'F_VIZ', 'tiers.tierset_id' => 1)

Which is a bit cleaner, since AR 'knows' about the implicit join table between features and tiers based on how your associations are set up.

bdon
  • 2,068
  • 17
  • 15
  • Thanks, this worked. Note that I had to modify it slightly because I wasn't searching for 'F_VIZ' directly, but for records that have 'VIZ' in their code name (also was looking for unique records, so added .uniq at the end): `Feature.where("code like ?", "%VIZ%").joins(:tiers).where("tiers.tierset_id" => 1).uniq` – tarunsachdeva Mar 11 '13 at 23:18
  • One strange thing is happening, though. On the query above, `.count` actually returns a count of all the non-unique entries too, not just the unique ones. Not sure if this is because my db (Postgres) is handling the join differently. Its not mission critical and isn't affecting my app...but it is strange. – tarunsachdeva Mar 11 '13 at 23:22
  • 1
    You should use `.length` instead of `.count`. The former will be evaluated in Ruby while the latter will be evaluated as `COUNT(*)` in the database. – bdon Mar 12 '13 at 03:18