Forum: Ruby on Rails find_by_sql returning nothing

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Andy (Guest)
on 2007-05-23 01:04
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
Andy (Guest)
on 2007-05-23 01:53
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.
This topic is locked and can not be replied to.