CODEWITHSUNDEEP
phpsqlzend-framework
Jose Boveda
Jose Boveda

Reputation: 190

Zend DB Select with multiple table joins

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

Upvotes: 3

Views: 7889

Answers (2)

elydelacruz
elydelacruz

Reputation: 155

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!

Upvotes: 1

stroop
stroop

Reputation: 249

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.

Upvotes: 6

Related Questions