Zend_DB_Select, fully re-build columns?

[Luerssen]

Some javascript/style in this post has been disabled (why?)

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.

[Hector Virgen]

Are you trying to select only columns from the table "finders"? If so, you can pass in empty arrays as the 3rd argument to joinLeftUsing:

$select = $db->select();

$select->from('finders');

$select->joinLeftUsing('finder_images', 'finder_id', array());

$select->joinLeftUsing('professions', 'profession_id', array());

$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);

-Hector

On Fri, Jan 9, 2009 at 1:38 PM, Adam Blake <[hidden email]> wrote:

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.

[weierophinney]

-- Adam Blake <[hidden email]> 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       | [hidden email]
Zend Framework           | http://framework.zend.com/