CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4
DollarChills
DollarChills

Reputation: 1086

Invalid syntax for integer

I'm receiving this error message when trying to match up integer ID's in my query us Postgres.

PG::InvalidTextRepresentation:ERROR: invalid input syntax for integer: "matches.team_id"

I have associations in place.

Stat

belongs_to :player
has_many :matches, foreign_key: 'match_id', primary_key: 'match_id'

Match

has_many :stats
has_many :players, through: :stats

Stats Controller

@player = Player.find(params[:id])    
@stats = Stat.where("player_id = ?", @player.id).joins(:matches)
.where('stats.team_id = ?', 'matches.team_id').select('SUM(stats.goals) AS goals')

My Matches database has two match_id rows that are the same and when I sum out the players goals it finds the players goals, but doubles the goals when displaying. So I figured if my query stated that only find where team_id's are the same then it would only generate 1 goal per match_id

Matches db

|id|match_id|team_id|total_score|
|10|  3433  |   1   |     3     |
|11|  3433  |   2   |     2     |

Stats db

|id|match_id|team_id|player_id|goals|
|12|  3433  |   1   |   333   |  1  |

Upvotes: 0

Views: 207

Answers (1)

Jim Stewart
Jim Stewart

Reputation: 17323

Change this:

where('stats.team_id = ?', 'matches.team_id')

to this:

where('stats.team_id = matches.team_id')

When you use substitution with ?, you're saying that the data for ? should be escaped. It's looking at the type of 'matches.team_id', sees that it's a string, and wraps it in quotes.

There may be a way to do that join without needing a custom where clause, but the root of your problem is the string escape mechanism.

Upvotes: 3

Related Questions