# Find\_by\_sql returning nothing

**URL:** <https://www.ruby-forum.com/t/find-by-sql-returning-nothing/104823>\
**Category:** Rails\
**Created:** [May 22, 2007, 11:04pm UTC](https://www.ruby-forum.com/t/find-by-sql-returning-nothing/104823 "2007-05-22T23:04:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\
**Post date:** [May 22, 2007, 11:04pm UTC](https://www.ruby-forum.com/t/find-by-sql-returning-nothing/104823/1 "2007-05-22T23:04:52Z")

</div>

I have a rather long query I’m passing into find\_by\_sql, but it doesn’t  
return anything. However, when I pull the Rails generated SQL from the  
log file and throw it into my db console (psql on Postgres), I do get  
results. That means that Rails is generating valid SQL, the database is  
returning the correct data, but it’s being lost for some reason after  
that.

My original call:

def self.getClonesInRegion(schema, chr, coord\_start, coord\_end)  
query = 'SELECT  
alset.clone\_id, co.\*,  
c.name, fcon.contig\_num, fco.coord\_start as map\_start,  
fco.coord\_end as map\_end  
FROM ’ + schema + '.coordinates co, ’ + schema +  
'.alignments\_coordinates alco, ’ + schema + '.alignments al, ’ + schema

- '.alignment\_sets alset, ’ + schema + '.clones c, ’ + schema +  
'.fpc\_clones fc, ’ + schema + '.fpc\_coordinates fco, ’ + schema +  
'.fpc\_contigs fcon ’ + ‘WHERE  
alset.rank = 0 AND  
al.alignment\_set\_id = alset.id AND alco.alignment\_id = al.id AND  
alco.coordinate\_id = co.id AND  
co.chr = ? AND  
((? \>= co.coord\_start AND ? \<= co.coord\_end) OR (? \>=  
co.coord\_start AND ? \<= co.coord\_start))  
AND c.id = alset.clone\_id AND fc.clone\_id = alset.clone\_id AND  
fco.id = fc.fpc\_coordinate\_id AND fcon.id = fco.fpc\_contig\_id  
AND fc.fpc\_build\_id = (SELECT id FROM fpc\_builds ORDER BY date  
DESC LIMIT 1)’  
find\_by\_sql [query, chr, coord\_start, coord\_start, coord\_end,  
coord\_start]  
end

I’ve tried taking out the variables, and just throwing the SQL Rails  
generated in the log (which works on the console):

def self.getClonesInRegion(schema, chr, coord\_start, coord\_end)  
result = find\_by\_sql([“SELECT alset.clone\_id, co.chr, co.coord\_start,  
co.coord\_end, c.name, fcon.contig\_num, fco.coord\_start as map\_start,  
fco.coord\_end as map\_end FROM ht06.coordinates co,  
ht06.alignments\_coordinates alco, ht06.alignments al,  
ht06.alignment\_sets alset, ht06.clones c, ht06.fpc\_clones fc,  
ht06.fpc\_coordinates fco, ht06.fpc\_contigs fcon WHERE alset.rank = 0 AND  
al.alignment\_set\_id = alset.id AND alco.alignment\_id = al.id AND  
alco.coordinate\_id = co.id AND co.chr = 16 AND ((10300000 \>=  
co.coord\_start AND 10300000 \<= co.coord\_end) OR (12500000 \>=  
co.coord\_start AND 10300000 \<= co.coord\_start)) AND c.id =  
alset.clone\_id AND fc.clone\_id = alset.clone\_id AND fco.id =  
fc.fpc\_coordinate\_id AND fcon.id = fco.fpc\_contig\_id AND fc.fpc\_build\_id  
= (SELECT id FROM fpc\_builds ORDER BY date DESC LIMIT 1)”])  
puts result.length  
return result  
end

I still get 0, and no data returned. If I substitute that long query  
with a small simple one, I do get data returned.  
I don’t think I’m hitting any data limit for amount returned, since I  
have other working queries that returns even more data.  
Any ideas why find\_by\_sql won’t work with just this query?

Thanks,  
Andy

---

<div class="post-metadata">

**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\
**Post date:** [May 22, 2007, 11:53pm UTC](https://www.ruby-forum.com/t/find-by-sql-returning-nothing/104823/2 "2007-05-22T23:53:11Z")

</div>

Sorry, just realized it was because of the Postgres schemas messing me  
up. My final subquery in didn’t specify a schema name, so that was what  
was causing the problem.