Query help

Him I'm building a kind of auction application except instead of buying and selling items, users can swap them with each other. I am struggling with the syntax for the queries. I have tables for items, auctions, bids and users set up as follows.

Item belongs_to :user has_one :auction

Auction belongs_to :user belongs_to :item has_many :bids

Bid belongs_to :user belongs_to :auction has_many :items

User has_many :auctions has_many :bids

Bid has many items as I would like the user to be able to offer more than one item in their bid, for example one user trades 3 items with another user for 1 more expensive item.

I'm trying to find all auctions that the current user has placed a bid on (I would like to display the item title of the auction).

At the moment I can only work out how to get the auction ID like so...

@bids = Bid.find(:all, :conditions => ["user_id = ?", session[:user][:id]])

Then in the view...

<% for bid in @bids %> <tr> <td><%= bid.auction_id %></td> </tr>

Finally, how can I make the search unique? IE if the user has placed multiple bids on one auctions, how can I make that auction appear only once in the results?

I think I could work it out ok in SQL, but I have found it difficult to find many resouces on ruby.

Any advice would be appreciated. Thanks.

Dan Smith wrote:

I think I could work it out ok in SQL, but I have found it difficult to find many resouces on ruby.

Any advice would be appreciated. Thanks.

You could always use find_by_sql and pass it whatever big nasty SQL you constructed...

Ah, I didn't know about find_by_sql, thanks. I think I have the query right now, but I'm still having a few problems putting with the ruby syntax. Can anyone see the problem here? Thanks.

@bids = Auction.find_by_sql [SELECT * FROM auctions WHERE id = (SELECT auction_id FROM bids WHERE user_id = ?, session[:user][:id])]

OK, I've got it to work, but only if I put in a literal value.. eg, @auctions = Auction.find_by_sql("SELECT * FROM auctions WHERE id = (SELECT auction_id FROM bids WHERE user_id = 2 )")

However, the following returns no results...

@sid = session[:user][:id] (@sid is 2) @auctions = Auction.find_by_sql("SELECT * FROM auctions WHERE id = (SELECT auction_id FROM bids WHERE user_id = @sid )")

Can anyone see what's wrong?

Yes. But look at your log/development.log and it will tell you too. You should have written:

@auctions = Auction.find_by_sql("SELECT * FROM auctions WHERE id = (SELECT auction_id FROM bids WHERE user_id = #{@sid} )")

Hmm, I'd have expected that it would be better couched as:

Assuming: class Bid    belongs_to :user    belongs_to :auction end class Auction    has_many :bids end class User    has_many :bids    has_many :auctions, :through => :bids end

user = User.find_by_id session[:user][:id] @auctions = user.auctions

or at the very least:

@auctions = Auction.find_by_sql("SELECT auctions.* FROM auctions WHERE auctions.id IN (SELECT bids.auction_id FROM bids WHERE bids.user_id = #{@sid})")

Note the use of "IN" rather than "="

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

Rob Biedenharn wrote:

user = User.find_by_id session[:user][:id] @auctions = user.auctions

I'd just add:

@auctions = user.auctions.uniq

Perhaps I answered too narrow a question. The OP wondered why his query was not returning the expected result set and it was because he hadn't used the string interpolation syntax (near as I can tell). As you point out, there is a Rails way to solve the problem better.