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.

Victor Smirnov · Accepted Answer · 2013-04-11 11:53:30Z

6

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

answered Apr 11, 2013 at 11:53

Victor Smirnov

3,8704 gold badges34 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

liyakat · Accepted Answer · 2013-04-11 11:50:24Z

2

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')

answered Apr 11, 2013 at 11:50

liyakat

11.9k2 gold badges42 silver badges47 bronze badges

1 Comment

Victor Smirnov Over a year ago

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.

웃웃웃웃웃 · Accepted Answer · 2013-04-11 11:57:31Z

1

$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.

edited Apr 11, 2013 at 11:57

answered Apr 11, 2013 at 11:52

웃웃웃웃웃

12k15 gold badges63 silver badges95 bronze badges

Collectives™ on Stack Overflow

Zend DB select only 1 table with multiple joins

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related