CODEWITHSUNDEEP
ruby-on-railsactiverecord
Eki Eqbal
Eki Eqbal

Reputation: 6017

How can I query fields in related join table?

I have user_contracts table which has contract_type and contract_id and it uses STI:

class UserContract < ActiveRecord::Base
  belongs_to :user
  belongs_to :contract, polymorphic: true
end

The attributes in UserContract looks like:

["id", "user_id", "quantity", "contract_id", "contract_type", "created_at", "updated_at"]

UserContract belongs to three types of contracts BondContract, EquityContract and DerivativeContract which looks like:

[193] pry(main)> EquityContract.attribute_names.inspect
=> "[\"id\", \"ticker\", \"name\", \"country\", \"currency\", \"instrument_type\", \"created_at\", \"updated_at\", \"industry_group\"]"

I'm creating a query which looks like:

  UserContract.
    joins("INNER JOIN bond_contracts ON bond_contracts.id = user_contracts.contract_id").
    joins("INNER JOIN equity_contracts ON equity_contracts.id = user_contracts.contract_id").
    joins("INNER JOIN derivative_contracts ON derivative_contracts.id = user_contracts.contract_id").
    where(user_id: user_ids)

How can I select records based on the related tables (BondContract, EquityContract and DerivativeContract) and if the related tables has currency == @currency unless @currency.nil? ? I want to get a list of UserContracts where related tables currency field is equal to some criteria.

Upvotes: 0

Views: 52

Answers (2)

user246672
user246672

Reputation:

If the intended use is not a toy, avoid polymorphic associations, in most instances. It won't scale (extra db hits AND nasty queries), it will negate indices (risking multiple full tablescans based on these sort of long queries) and it's impossible to enforce referential integrity in the dbms.

Instead: Use HMT and a join table for each contract type. To grab all of one type of contract per User, use parameterized scopes in each Contract type ... it will avoid much SQL muck (some may be needed, but try to do it the standard way first). To grab all contracts for a certain User, just combine all contracts types.

  scope :by_user, ->(user) { where(user: user) }
  scope :by_industry_group, ->(industry_group) { where(industry_group: industry_group) }
  ...


  # Then
  SomeContract.by_user(john).by_industry_group(airlines)

```

Another idea is to use a pseudo-model presenter Contract class which aggregates all contracts from multiple underlying models for displaying on forms... it won't have full AR support, but it can make it easier to deal with multiple Contract types in a sanely-normalized, db architecture.

Also, see the Rails Guides

Upvotes: 1

tillmo
tillmo

Reputation: 616

Use something like:

query = UserContract.joins... # as above
query = query.where(bond_contracts: {currency: @currency}) if @currency.present?

Upvotes: 1

Related Questions