CODEWITHSUNDEEP
sqlruby-on-railssql-likepolymorphic-associations
Nathan Lauer
Nathan Lauer

Reputation: 391

Rails search by SQL LIKE through polymorphic association

I have a rails api, with the following models:

Players::NflPlayer

class Players::NflPlayer < ApplicationRecord
  #################### Associations
  has_many :fantasy_players, class_name: "Leagues::FantasyPlayer", as: :player_entity, dependent: :destroy

 #################### Validations
  validates_presence_of :name

Players::TeamPlayer

class Players::TeamPlayer < ApplicationRecord
  #################### Associations
  has_many :fantasy_players, class_name: "Leagues::FantasyPlayer", as: :player_entity, dependent: :destroy

  #################### Validations
  validates_presence_of :name

And Leagues::FantasyPlayer

class Leagues::FantasyPlayer < ApplicationRecord
  #################### Associations
  belongs_to :league, class_name: "Leagues::League"
  belongs_to :player_entity, polymorphic: true

In essence, a fantasy player can either be an NFL player, or a "Team" player, and that association is captured by the polymorphic variable "player_entity." Both the Players::NflPlayer and Players::TeamPlayer tables must have a column "name"

I'm trying to a do a search for certain fantasy players, by name, where the search term refers to the "name" column of either Players::NflPlayer or Players::TeamPlayer tables. This is the query I have so far to do this: 

Leagues::FantasyPlayer.where(
    league_id: league_id,
    player_entity: Players::NflPlayer.where("name LIKE :search_term", search_term: "#{search_term}%") || Players::TeamPlayer.where("name LIKE :search_term", search_term: "#{search_term}%")
  )

However, this only seems to load Leagues::FantasyPlayers that are associated with the PLayers::NflPlayer table, and not the Players::TeamPlayer table.

I think maybe I have to do a joins on those two tables first, but I'm not sure how to do that in this single query.

Any help would be much appreciated, thank you!

Upvotes: 0

Views: 281

Answers (1)

Igor Drozdov
Igor Drozdov

Reputation: 15045

Unfortunately, the following line:

Players::NflPlayer.where("name LIKE :search_term", search_term: "#{search_term}%") || Players::TeamPlayer.where("name LIKE :search_term", search_term: "#{search_term}%")

returns the first condition of the ||. As a result you receive:

Leagues::FantasyPlayer.where(
    league_id: league_id,
    player_entity: Players::NflPlayer.where("name LIKE :search_term", search_term: "#{search_term}%")
  )

In order to make it work, you need SQL's OR, Rails 5 supports it the following way:

Leagues::FantasyPlayer.where(
  league_id: league_id,
  player_entity: Players::NflPlayer.where("name LIKE :search_term", search_term: "#{search_term}%")
).or(
  Leagues::FantasyPlayer.where(
    league_id: league_id,
    player_entity: Players::TeamPlayer.where("name LIKE :search_term", search_term: "#{search_term}%")
  )
)

Upvotes: 2

Related Questions