# Need translation of sql query to :find equivalent

**URL:** <https://www.ruby-forum.com/t/need-translation-of-sql-query-to-find-equivalent/150250>\
**Category:** Rails\
**Created:** [October 6, 2008, 4:24pm UTC](https://www.ruby-forum.com/t/need-translation-of-sql-query-to-find-equivalent/150250 "2008-10-06T16:24:25Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [October 6, 2008, 4:24pm UTC](https://www.ruby-forum.com/t/need-translation-of-sql-query-to-find-equivalent/150250/1 "2008-10-06T16:24:25Z")

</div>

I have this query, I have tried a few permutations to translate it into  
a :find equivalent, as a newbie this is a bit trickier than i first  
envisaged.

heres the core sql:  
select resulttype from results where  
id in  
(  
select result\_id from outcomes o1 where  
o1.outcome\_date = (select max(o2.outcome\_date) from outcomes o2 where  
o2.testcase\_id = o1.testcase\_id  
and o1.testcase\_id =‘1’));

can you give me a few pointers, does it require a join clause from the  
results table to the outcomes, or can rails do this easier?

---

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [October 6, 2008, 5:33pm UTC](https://www.ruby-forum.com/t/need-translation-of-sql-query-to-find-equivalent/150250/2 "2008-10-06T17:33:28Z")

</div>

Brad S. wrote:  
Try

@result = Resulttypes( :all, :conditions =\> [‘id in ( select result\_id  
from outcomes o1 where o1.outcome\_date = (select max(o2.outcome\_date)  
from outcomes o2 where o2.testcase\_id = o1.testcase\_id and  
o1.testcase\_id =‘1’))’]

This should work, I am doing seomthing similiar only I am say ‘NOT IN.’

---

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [October 6, 2008, 5:41pm UTC](https://www.ruby-forum.com/t/need-translation-of-sql-query-to-find-equivalent/150250/3 "2008-10-06T17:41:38Z")

</div>

i cannot get the brackets right, you seem to have more brackets on the (  
than on the ), when i mess around with this it just throws all kinds of  
errors.

here is another query i am trying to write using :find

Result.find(:all, :conditions =\> ‘resulttype = (select resulttype from  
results where id = 1)’)

when i run this with debug

\<%= debug Result.find(:all, :conditions =\> ‘resulttype = (select  
resulttype from results where id = 1)’) %\>

i get:  
resulttype = ‘pass’  
id = ‘1’

but when i remove debuy, i just get ####, adding a h, like this  
\<%= h Result.find(:all, :conditions =\> ‘resulttype = (select resulttype  
from results where id = 1)’) %\>

i get #Result:0x395f080

can anyone point me to a good source to understand more about the :find,  
because its quite obscure on the rails reference.

---

<div class="post-metadata">

**Author:** ![bskaggs](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ab097/32.png) [@bskaggs](https://www.ruby-forum.com/u/bskaggs)\
**Post date:** [October 6, 2008, 5:59pm UTC](https://www.ruby-forum.com/t/need-translation-of-sql-query-to-find-equivalent/150250/4 "2008-10-06T17:59:05Z")

</div>

On 6 Oct 2008, at 16:41, Brad S. wrote:

> 

> \<%= h Result.find(:all, :conditions =\> ‘resulttype = (select  
> resulttype  
> from results where id = 1)’) %\>
> 
> i get #Result:0x395f080

\<%= %\> just calls to\_s on the result inside it. The to\_s on an array  
is just the concatenation of the result of to\_s on the elements of the  
array, and the default to\_s on an AR object is unhelpful. It’s up to  
you do to something like

\<% @some\_results.each do |result|  
date: \<%= result.created\_at %\>  
value: \<%= result.value %\>  
…  
\<% end %\>