Re: [fw-core] Zend_DB_Select, fully re-build columns?

-- Adam Blake <lluerssen@gmail.com> wrote
(on Friday, 09 January 2009, 11:38 PM +0200):
> Hi guys, using latest svn copy of ZF, i got trouble.
>
>
> <?php
>
> // ... require other code goes here ...
>
> $profession_rewrite = 'dodo';
>
> $select = $db->select();
> $select->from('finders');
> $select->joinLeftUsing('finder_images', 'finder_id');
> $select->joinLeftUsing('professions', 'profession_id');
> $select->where('finder_moderated = ?', 1);
> $select->where('profession_rewrite = ?', $profession_rewrite);
> $select->order('finder_image_path_preview DESC');
> $select->order('finder_created_at DESC');
> $select->limitPage($page, $per_page);
>
> print $select;
>
> ?>
>
>
> will return to me:
>
>
> SELECT `finders`.*, `finder_images`.*, `professions`.* FROM `finders` LEFT
> JOIN `finder_images` ON `finder_images`.finder_id = `finders`.finder_id
> LEFT JOIN `professions` ON `professions`.profession_id = `finders
> `.profession_id WHERE (finder_moderated = 1) AND (profession_rewrite =
> 'other') ORDER BY `finder_image_path_preview` DESC, `finder_created_at`
> DESC LIMIT 20
>
>
> how i can re-build it, into:
>
>
> SELECT * ...
>
>
> because, joined `finder_images` table rewrites `finder_id` of `finders` table.

Don't use joinLeftUsing(); use joinLeft():

$select->joinLeft('finder_images', 'finder_images.finder_id = finders.id');

This will create the SQL such that no columns are pulled from the
finder_images table. If you *do* want columns pulled from the joined
tables, then you should likely create table aliases:

$select->joinLeft(array('i' => 'finder_images'), 'i.finder_id = finders.id, '*');

This will select the finder_images columns as i.* -- which will prevent
column collisions.

--
Matthew Weier O'Phinney
Software Architect | matthew@zend.com
Zend Framework | http://framework.zend.com/