Geek Answers Handbook

Zend DB selects only 1 table with multiple joins

I am using Zend DB to generate a query using the following code:

$table->select() ->setIntegrityCheck(false) //required for multi-table join ->from('modules') ->joinInner( 'basket_modules', 'modules.id = basket_modules.id') ->joinInner( 'baskets', 'baskets.id = basket_modules.basket_id') ->where('baskets.id = ?', $this->id);

This generates SQL:

 SELECT modules.*, basket_modules.*, baskets.* FROM modules INNER JOIN basket_modules ON modules.id = basket_modules.id INNER JOIN baskets ON baskets.id = basket_modules.basket_id WHERE (baskets.id = '3')

My problem here is in the SELECT part, it selects all 3 tables, not just the modules I want. Therefore, the query I would like to create is as follows:

 SELECT `modules`.* FROM `modules` #etc...

How can i do this? If I manually edit the request and run it, it will return to me what I want, so there should be no problems with the syntax.

+4
source share
3 answers

Please see the example in the Zend_Db_Select manual. Highlight Example # 13.

To avoid selecting columns from a table, use an empty array for the column list. This usage also works in the from () method, but usually you want some columns from the main table in your queries, while you might not need columns from the joined table.

 $select = $db->select() ->from(array('p' => 'products'), array('product_id', 'product_name')) ->join(array('l' => 'line_items'), 'p.product_id = l.product_id', array() ); // empty list of columns
+6
source

you can specify a column name for another table and main table as shown below

 $table->select() ->setIntegrityCheck(false) //required for multi-table join ->from('modules',array('modules.*')) ->joinInner( 'basket_modules', 'modules.id = basket_modules.id',array('basket_modules.id')) ->joinInner( 'baskets', 'baskets.id = basket_modules.basket_id',array('baskets.id')) ->where('baskets.id = ?', $this->id);

so sql will look like

 SELECT modules.*, basket_modules.id, baskets.id FROM modules INNER JOIN basket_modules ON modules.id = basket_modules.id INNER JOIN baskets ON baskets.id = basket_modules.basket_id WHERE (baskets.id = '3')
+2
source 
 $table->select() ->setIntegrityCheck(false) //required for multi-table join ->from('modules') ->joinInner( 'basket_modules', 'modules.id = basket_modules.id',array('')) ->joinInner( 'baskets', 'baskets.id = basket_modules.basket_id',array('')) ->where('baskets.id = ?', $this->id);

Give an empty array as the third join parameter, otherwise it will select the entire field from the joined table. If you want some fields to specify the names of the fields in the array when connecting.

+1
source

More articles:


All Articles