Find_by_sql returning nothing

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

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 forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs