#1510 new
Chris Corbyn

has n association with :through and :via breaks due to ambiguous INNER JOIN condition

Reported by Chris Corbyn | May 13th, 2011 @ 11:12 AM

Given the legacy schema for an auction platform:

CREATE TABLE user (
  userid INT PRIMARY KEY
)

CREATE TABLE auction (
  auctionid INT PRIMARY KEY,
  userid INT
)

CREATE TABLE auctionbid (
  bidid INT PRIMARY KEY,
  userid INT,
  auctionid INT
)

Attempting to add a #received_bids accessor on User (to fetch all bids received from other users on auctions the user owns), to do the three-table JOIN across user, auction and auctionbid, produces an error:

Column 'userid' in on clause is ambiguous (code: 1052, sql state: 23000, query: SELECT auctionbid.bidid, auctionbid.amount, auctionbid.status, auctionbid.auctionid FROM auctionbid INNER JOIN auction ON auctionbid.auctionid = auction.auctionid INNER JOIN user ON auction.userid = userid WHERE auction.userid = 97639 GROUP BY auctionbid.bidid, auctionbid.amount, auctionbid.status, auctionbid.auctionid ORDER BY auctionbid.bidid

The query is nearly correct, but it lacks the storage_name on the second join back to user. Here is the expected query:

SELECT `auctionbid`.`bidid`, `auctionbid`.`amount`, `auctionbid`.`status`, `auctionbid`.`auctionid` FROM `auctionbid` INNER JOIN `auction` ON `auctionbid`.`auctionid` = `auction`.`auctionid` INNER JOIN `user` ON `auction`.`userid` = `user`.`userid` WHERE `auction`.`userid` = 97639 GROUP BY `auctionbid`.`bidid`, `auctionbid`.`amount`, `auctionbid`.`status`, `auctionbid`.`auctionid` ORDER BY `auctionbid`.`bidid`

And here is the code used to in the model (excluding the irrelevant fields):

class User
  include DataMapper::Resource
  storage_names[:default] = 'user'
  property :id, Serial, :field => 'userid'
  has n, :auctions, :child_key => [ 'userid' ]
  has n, :received_bids, 'Bid', :through => :auctions, :via => :bids # Doesn't work
end

class Auction
  include DataMapper::Resource
  storage_names[:default] = 'auction'
  property :id, Serial, :field => 'auctionid'
  belongs_to :user, :child_key => [ 'userid' ]
  has n, :bids, :child_key => [ 'auctionid' ]
end

class Bid
  include DataMapper::Resource
  storage_names[:default] = 'auctionbid'
  property :id, Serial, :field => 'bidid'
  belongs_to :user, :child_key => [ 'userid' ]
  belongs_to :auction, :child_key => [ 'auctionid']
end

Comments and changes to this ticket

  • Chris Corbyn

    Chris Corbyn May 13th, 2011 @ 09:52 PM

    UPDATE. I started poking around in the internals of dm-do-adapter. The problem is in the method #property_to_column_name. It only adds the alias if it's a String or a Boolean:

            # @api semipublic
            def property_to_column_name(property, qualify)
              column_name = ''
              case qualify
                when true
                  column_name << "#{quote_name(property.model.storage_name(name))}."
                when String
                  column_name << "#{quote_name(qualify)}."
              end
                
              column_name << quote_name(property.field)
            end
    

    The model for my User actually had the storage_name specified as a symbol.

    I guess DM should either error when a symbol is used in a storage name, or handle that case in the case..when given here.

    I'm glad to say this was actually my own mistake and that DM continues to impress me. In our current system we would definitely have resorted to using SQL (or multiple queries) if we had to fetch models across more than one join.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Pages