[Dbix-class] Newbie question on arbitrary joins

[Amelia Ireland]

Hello,

I have a question on joining tables without using the relationships  
defined in the DB schema. I can formulate the query in standard SQL,  
but getting it into DBIx::Class speak is proving a little tricky.

I have the following set up:

association JOIN term ON association.term_id=term.id

association LEFT JOIN graph_path ON  
association.term_id=graph_path.term2_id

association JOIN gene_product ON  
association.gene_product_id=gene_product.id

What I want to do is an 'artificial' join on the association table to  
match up tables by gene_product_id so that I don't have to include the  
extra table in my search.

This is the SQL, with the line I need to DBIx::Class-ize starred:

SELECT term.acc, term_2.acc, me.gene_product_id
FROM association me
LEFT JOIN graph_path graph_path_relations ON  
( graph_path_relations.term2_id = me.term_id )
JOIN term term ON ( term.id = me.term_id )

** JOIN association assoc ON ( assoc.gene_product_id =  
me.gene_product_id ) ***

LEFT JOIN graph_path graph_path_relations_2 ON  
( graph_path_relations_2.term2_id = assoc.term_id )
JOIN term term_2 ON ( term_2.id = assoc.term_id )

WHERE graph_path_relations.term1_id = 'x' AND  
graph_path_relations_2.term1_id = 'y'

Can the 'from' and 'join' arguments in a ResultSet be mixed and  
matched to create this result without having to do it all via the  
'from' argument?

Thanks a lot for any help or advice!

Amelia.

--
Amelia Ireland
GO Editorial Office
http://www.berkeleybop.org || http://www.ebi.ac.uk
BBOP Plant Project: http://bbopgarden.blogspot.com