Zend DB Select with multiple table joins

Question

Hey all,

Trying to replicate the following query 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;

Specifically some issues I'm running into are doing multiple table joins in Zend. I'm not sure how to do the join across multiple tables using their query builder.

Any help is appreciated!

J

Answer 1

Something the likes 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 get you somewhere. I didn't test it, so there might be some errors in there.

Answer 2

The format:

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

Works great!
Thank both of you posters for this info! Helped me solve quite a doozy! (Abstract Table Models oh my!!) Lol

Ps: To others about to utilize the same format detailed above remember that column names will be overwritten by latter tables with same column names. Happy Coding!