Re: [fw-db] how to use zend db with joins

<?php
class Model_Managers_AssignUserToMenu extends Zend_Db_Table_Abstract {

protected $_name = 'GNL_USER_MENU';
......

}



$table = new Model_Managers_AssignUserToMenu();

$select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select = $table->select(Zend_Db_Select::DISTINCT);
$select->setIntegrityCheck(FALSE)
// ->distinct(true)
->join('GNL_ARC_MENU', 'GNL_ARC_MENU.MENU_ID = GNL_USER_MENU.MENU_ID')
->order('GNL_USER_MENU.MENU_ID');

$result = $table->fetchAll($select);

I would like to raise a question here since ZEND_DB and JOIN are in discussion;

i tried to use DISTINCT with ZEND_DB_SELECT's JOIN...but seems like it doesnt work...with this email i'm attaching the code i was using it with..

Thanx in advance

On Wed, Jun 23, 2010 at 1:12 PM, Jeroen Keppens <jeroen.keppens@gmail.com> wrote:

I would say that Zend_Db_Select is the way to go to do your joins in selects:

Example from the docs:

1. // Build this query:
2. //   SELECT p."product_id", p."product_name"
3. //   FROM "products" AS p JOIN "line_items" AS l
4. //     ON p.product_id = l.product_id
5.  
6. $select = $db->select()
7.              ->from(array('p' => 'products'),
8.                     array('product_id', 'product_name'))
9.              ->join(array('l' => 'line_items'),
10.                     'p.product_id = l.product_id',
11.                     array() ); // empty list of columns

If you want fields from the second table add the column names in the example where it says "empty list of columns".

If you want to do updates on multiple tables using joins, you'll have to use raw queries since ZF db->update does not support that as far as I know.

Hope this helps!

Wkr

Jeroen

http://blog.amazium.com

On 23 Jun 2010, at 10:58, zensys wrote:

Just a general question on how to best utilise zend db. I have a few tables
which I want to display with join statements and update afterwards. Not a
very complicated schema but quite some fields which I want to select
individually. To my limited understanding of DB I have the following
options:

- instantiate an adapter and just go from there using db->query() etc.
- use db select
- instantiate a db table for each of my tables and use joins (or db select?)
from there.

From what I've read in the manual it seems like only use an adapter gives

the least overhead. Db select is only for complicated queries and anyway you
cannot update with joins. Instantiate db tables for each table also sounds
like some overhead and I do not see the advantages.

Anyone any thoughts on this?

And another thing: do I need to instantiate an adapter but have the
resource.db.params in my application.ini . In that case, is the adapter
already instantiated and if so how do I reference it. If not, using the
db_factory? But it asks for parameters.
--
View this message in context: http://zend-framework-community.634137.n4.nabble.com/how-to-use-zend-db-with-joins-tp2265294p2265294.html
Sent from the Zend DB mailing list archive at Nabble.com.

--
Regards,
YD