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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
