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
--
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
-~----------~----~----~----~------~----~------~--~---