Invalid syntax for integer

Question

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  |

Jim Stewart · Accepted Answer · 2016-11-16 03:13:12Z

3

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.

answered Nov 16, 2016 at 3:13

Jim Stewart

17.4k5 gold badges78 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invalid syntax for integer

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related