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 %>
| <%= bid.auction_id %> |
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 S. 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?
On Mar 21, 2008, at 4:44 PM, Dan S. wrote:
@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} )”)
On Mar 21, 2008, at 7:54 PM, s.ross wrote:
(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 B. http://agileconsultingllc.com
[email protected]
On Mar 21, 2008, at 5:40 PM, Rob B. wrote:
@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} )”)
class User
@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
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.
Rob B. wrote:
user = User.find_by_id session[:user][:id]
@auctions = user.auctions
I’d just add:
@auctions = user.auctions.uniq