Zend DB select only 1 table with multiple joins

Question

I'm 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 the 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 with the SELECT part, it's selecting all 3 tables instead of just modules, which is the one I want. So the query I would want to generate is:

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

How can I do this? If I edit the query manually and run it, it returns what I want so there shouldn't be a problem with the syntax.

score 6 · Accepted Answer · answered Apr 11 '13 at 11:53

Please look at the example in the manual Zend_Db_Select. Scroll to the Example #13.

To select no columns from a table, use an empty array for the list of columns. This usage works in the from() method too, but typically you want some columns from the primary table in your queries, whereas you might want no columns from a 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

score 2 · Answer 2 · answered Apr 11 '13 at 11:50

you can specify column name for other table and main table like 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 be 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')

I think `array('basket_modules.id')` is wrong because we don't need to specify table name. Table alias is added automatically. This is why I think correct code would be `array('id')` or even empty array. — Victor Smirnov, Apr 11 '13 at 12:03

웃웃웃웃웃 · Answer 3 · 2013-04-11T11:57:31.277

$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 a empty array as the third parameter of join otherwise it will select all the field from the table joined.If you want some fields then specify field names in the array while joining.

Zend DB select only 1 table with multiple joins

3 Answers3