Hi all,
I have some difficulties putting together a query using four tables,
maybe someone can help me out on how to write the code:
table: organisms (id, organism)
1 Organism1
2 Organism2
3 Organism3
and so on…
table:genes (id, name, organism_id, location, other columns)
1 Gene1 1 chr1
2 Gene2 1 chr1
3 Gene3 1 chr1
4 Gene 4 2 chr2
table:intexos(id, gene_id,start,finish,sequence)
1 1 23 45 sequence
2 1 46 70 sequence
3 1 71 98 sequence
4 2 101 123 sequence
5 2 124 156 sequence
6 2 157 200 sequence
table: rnas (id,name,organism_id, location, start, finish, sequence)
1 rna1 1 chr1 26 42 sequence
2 rna2 1 chr1 127 153 sequence
On normal language, here are the relations:
Each organism (table organisms) has a number of genes (table genes).
Each gene can be broken down into introns and exons (table intexos,
coding and non coding parts). Organisms also carry rna genes (table
rnas), for which the same coordinate system is used. I now need to
check which rna genes overlap with intexos (wich is a child of genes).
The structure is not really up for discussion at this point for
various reasons that have to do with performance and other
considerations.
So, what I need the query to do is the following:
Find all rnas whose start/finish fall WITHIN the start/finish of an
intexos from the same organism from the same location (in this case a
chromosome).
I can in principle write the sql query but I cant get it to limit the
search to a particular organism_id. In the application the user would
get a list of organisms, and then perform the query discussed above
for one particular organism only. I thought I could do it by going via
@organism = Organism.find(params[:id])
@rnas = @organism.rnas.find_by_sql(“a query here”)
But that gives me a list of ALL elements from the rnas table that fall
within the boundaries of an element from intexos rather then only
those elements from a particular organism. Drives me nuts 
Any suggestions?
Cheers,
Marc