# Query help

**URL:** <https://www.ruby-forum.com/t/query-help/132611>\
**Category:** Rails\
**Created:** [March 21, 2008, 8:47pm UTC](https://www.ruby-forum.com/t/query-help/132611 "2008-03-21T20:47:50Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 21, 2008, 8:47pm UTC](https://www.ruby-forum.com/t/query-help/132611/1 "2008-03-21T20:47:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 21, 2008, 9:20pm UTC](https://www.ruby-forum.com/t/query-help/132611/2 "2008-03-21T21:20:39Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 21, 2008, 10:36pm UTC](https://www.ruby-forum.com/t/query-help/132611/3 "2008-03-21T22:36:48Z")

</div>

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])]

---

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 22, 2008, 12:44am UTC](https://www.ruby-forum.com/t/query-help/132611/4 "2008-03-22T00:44:06Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 22, 2008, 12:54am UTC](https://www.ruby-forum.com/t/query-help/132611/5 "2008-03-22T00:54:59Z")

</div>

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} )”)

---

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 22, 2008, 1:40am UTC](https://www.ruby-forum.com/t/query-help/132611/6 "2008-03-22T01:40:45Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 22, 2008, 4:06am UTC](https://www.ruby-forum.com/t/query-help/132611/7 "2008-03-22T04:06:37Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![drsharp](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/e9bcb4/32.png) [@drsharp](https://www.ruby-forum.com/u/drsharp)\
**Post date:** [March 22, 2008, 3:16am UTC](https://www.ruby-forum.com/t/query-help/132611/8 "2008-03-22T03:16:34Z")

</div>

Rob B. wrote:

> user = User.find\_by\_id session[:user][:id]  
> @auctions = user.auctions

I’d just add:

@auctions = user.auctions.uniq