Ambiguous Column References in Rails
I was messing around on the console today, and ran into an interesting problem when joining a model to another model twice. An ambiguous column reference. Haven’t seen that one before; time to investigate.
The architecure for this example is:
class Wallet < ActiveRecord::Base belongs_to :primary_card, class_name: "Card", dependent: :destroy belongs_to :secondary_card, class_name: "Card", dependent: :destroy end class Card < ActiveRecord::Base has_one :wallet end
Say you want to sum up the total amount on all the primary cards that fit a subset of wallets. You’d run this:
This works great. Doing the same thing for the secondary card is also fantastic. On my computer, running this type of query on a set of 40k records takes ~40ms per query. Now, take this same issue and scale it up a few orders of magnitude. Doing these queries separately takes too long. In this contrived example, you’re just using this information to add the two totals together to find out the total amount for a single wallet. So, you’re going to optimize this and get both sums at once. You try the following out:
If you’re on Postgres, you’ll find the following:
PG::AmbiguousColumn: ERROR: column reference "amount" is ambiguous
For MySQL you’ll see:
SQL Error: Column 'amount' in field list is ambiguous.
Obviously, this is because both joins have an ‘amount’ column, so the database doesn’t know which to sum. Even though they’re from the same table, this is still an issue, at least with Postgres 9.5.0. When this happened to me, I went to my first default resource, namely google. Searching google and stackoverflow are my staples. Whenever anything goes wrong, 99%+ of the time someone else has had the exact same problem. In all reality, that has to be much closer to 99.999% of the time, but sometimes results get buried. After reading through some incredibly long and complex SO solutions that looked much too convoluted (or elegant, take your pick) I remembered that most of the time, symbols and strings are completely interchangeable. Yes, that’s not true all the time, but it’s a great place to start.
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "card"
Damn. Now what the hell does that mean? Fast forward a few minutes, and what it means is that I’ve stared at a computer long enough for one day that I forgot to pluralize the table name to match what it’s called in postgres.
(71.7ms) SELECT SUM(cards.physical_address_months) FROM "wallets" INNER JOIN "cards" ON "cards"."id" = "wallets"."primary_card_id" INNER JOIN "cards" "secondary_cards_wallets" ON "secondary_cards_wallets"."id" = "wallets"."secondary_card_id" 4764742542
Exactly what I wanted. Also, for my dataset at hand, ~10ms/12% quicker than doing the queries seperately. I’ll dive into more exact benchmark numbers with varied data set sizes in a future post.
If you enjoy having free time and the peace of mind that a professional is on your side, then you’d love to have me work on your project.