CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4associations
lurker
lurker

Reputation: 58284

Selecting records based upon conditions on multiple associations that use the same table

I have what is actually a common situation in Rails where I have a model that has two associations. I want to be able to search for specific records defined by the model based upon conditions on either one or both of these associations.

The twist is that these two associations use the same table.

Here's the main model:

class Resource < ActiveRecord::Base
  belongs_to :primary_item, class_name: 'Item', foreign_key: 'primary_item_Id'
  belongs_to :secondary_item, class_name: 'Item', foreign_key: 'secondary_item_Id'
  ...

And the Item model:

class Item < ActiveRecord::Base
  has_many :res_primary, class_name: 'Resource', foreign_key: 'primary_item_Id'
  has_many :res_secondary, class_name: 'Resource', foreign_key: 'secondary_item_Id'
  ...

Let's suppose an Item has a string field called name. It's just the name of the item. I want to be able to find all of the resources that have a primary and/or secondary item that is like a given name. Here's what that looks like (I think) if I am just filtering for a primary item:

@resources.joins(:primary_item)
          .where("#{Item.table_name}.name like #{primary_search_string}")

The primary_search_string is just the string I want to match in the name. This works fine. A similar search works for the secondary items.

Now suppose I'd like the user to be able to search for resources that have either a given primary item name, a given secondary item by name, or both (each with its own name). I would have a primary_search_string and a secondary_search_string with independent values. One of them could be nil which would mean I don't want to narrow the search based upon that string. What I want is to filter the resources by either or both strings depending upon whether they are nil. Here is what I ended up with which works but seems awkward:

@resources = <some query that obtains an active record relation of resources>

if primary_search_string then
  @resources = @resources.joins(:primary_item)
                         .where("#{Item.table_name}.name like #{primary_search_string}")
  if secondary_search_string then
    @resources = @resources.joins(:secondary_item)
                           .where("secondary_items_#{Item.table_name}.name like #{secondary_search_string}")
  end
elsif secondary_search_string then
  @resources = @resources.joins(:secondary_item)
                        .where("#{Item.table_name}.name like #{secondary_search_string}")
end

Note how that if I am only joining one table, the table's name is known by Item.table_name. However, if I have to join both tables, then Rails must distinguish the second instance of the table by specifying the name further with the association name: secondary_items_#{Item.table_name}.

My question is whether there's a somewhat simpler way of handling this that doesn't involve having to reference the table names of the associations in the where clauses? Since I am referencing the table names of the associations, and the table name may be different depending upon whether I'm joining one of them or both of them, I end up checking the search string for nil, which is a way to determine whether I'm joining the Item table more than once. In this particular program example, I can check for it and live with it being awkward. But what if I didn't know whether @resources was previously joined to the primary items and I wanted to filter based upon the secondary items? I wouldn't know, in that case, what the associations table name would be to use in the where clause since I wouldn't know if it were already joined or not.

I suspect there may be a better way here, which is the essence of my question.

Upvotes: 0

Views: 836

Answers (1)

dlehman
dlehman

Reputation: 338

If you have two separate associations, but they are both to the same type of child object (eg. Resource), then instead of joins/includes, you could just focus on finding the Resource records that match either the primary_item_id or secondary_item_id of the parent Item record you want.

Rails 3/4 doesn't natively support OR queries, but one way to brute-force it is by finding IDs of Resources that belong to the Item as a primary or secondary association. (This is obviously inefficient because you are doing multiple database queries.)

ids = Resource.where(primary_item_id: @item.id).map(&:id)
ids << Resource.where(secondard_item_id: @item.id).map(&:id)
@special_resources = Resource.where(id: ids, name: 'Some Special Name')

Rails 5 supports 'or' queries, so it's much simpler:

resources = Resource.where(primary_item_id: @item.id).or(secondary_item_id: @item.id)
@special_resources = resources.where(name: 'Some Special Name')

Upvotes: -1

Related Questions