Zend DB Select with multiple table joins

Question

Attempted to replicate the following request using Zend_Db_Select . Any pointers?

 SELECT compounds.id as compounds_id, reactions.id as reactions_id, reaction_compound.number as reaction_compound_number FROM compounds, reactions, reaction_compound WHERE compounds.id IN (68,74,112) AND compounds.id = reaction_compound.compound AND reactions.id = reaction_compound.reaction; 

In particular, some of the problems I encountered are joining multiple tables in Zend. I am not sure how to join multiple tables using my query designer.

Any help is appreciated!

J

Answer 1

Sort of:

 $compoundIds = array(68,74,112); $select = $db->select() ->from('compounds', array('compounds_id' => 'id') ->where('compounds.id in ( ? )', $compoundIds) ->join('reaction_compound', 'compounds.id = reaction_compound.compound', array('reaction_compound_number' => 'number')) ->join('reactions', 'reactions.id = reaction_compound.reaction', array('reaction_id' => 'id'); 

That should lead you somewhere. I have not tested it, so there may be some errors.

Answer 2

Format:

 $db->select()->from('table_name')->join('...')->join('...') 

It works great! Thank you both posters for this information! Helped me solve a pretty doozy! (Abstract models of tables oh my !!) Lol

Ps: For others who use the same format as described in detail above, remember that the column names will be overwritten by the last tables with the same column names. Happy coding!