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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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... -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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])] -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Mar 21, 2008, at 4:44 PM, Dan Smith wrote:> > 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} )") --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Mar 21, 2008, at 7:54 PM, s.ross wrote:> On Mar 21, 2008, at 4:44 PM, Dan Smith wrote: >> 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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Rob Biedenharn wrote:> user = User.find_by_id session[:user][:id] > @auctions = user.auctionsI''d just add: @auctions = user.auctions.uniq -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Mar 21, 2008, at 5:40 PM, Rob Biedenharn wrote:> On Mar 21, 2008, at 7:54 PM, s.ross wrote: >> On Mar 21, 2008, at 4:44 PM, Dan Smith wrote: >>> 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 "=" > > -RobPerhaps 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---