| 
Software
 » 
PHP
 » 
Zend Framework Community
 » 
Zend DB

Confusion which way to use Zend DB, use Model-relationship or SQL select statements

View: New views
4 Messages — Rating Filter:   Alert me  

Confusion which way to use Zend DB, use Model-relationship or SQL select statements

by fossil :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Hi all I am confused,
Which is the better way to use zend db, is it by writing pure own sql queries
And use in select statements)as in PART A below) or use models of tables and join with
Given model relationships (PART B below)
------------------------------------------------------------------
Example PART A
------------------------------------------------------------------
$select = $db->select()
    ->from(array('p' => 'products'),
        array('product_id'))
    ->join(array('l' => 'line_items'),
        'p.product_id = l.product_id',
        array('line_items_per_product' => 'COUNT(*)'))
    ->group('p.product_id')
    ->having('line_items_per_product > 10');

------------------------------------------------------------------
<?php
$db = Zend_Db::factory( ...options... );
$select = $db->select();

------------------------------------------------------------------
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';

$result = $db->fetchAll($sql, 2);
------------------------------------------------------------------

////////////////          OR          /////////////////////

------------------------------------------------------------------
Example PART B
------------------------------------------------------------------
class Products extends Zend_Db_Table_Abstract
{
    protected $_name            = 'products';
    protected $_dependentTables = array('BugsProducts');
}

class Bugs extends Zend_Db_Table_Abstract
{
    protected $_name            = 'bugs';

    protected $_dependentTables = array('BugsProducts');

    protected $_referenceMap    = array(
        'Reporter' => array(
            'columns'           => 'reported_by',
            'refTableClass'     => 'Accounts',
            'refColumns'        => 'account_name'
        ),

--------------------------------------------------------------------------------
<?php
$bugs = new Bugs();
$row = $bugs->fetchRow('bug_id = 1');
------------------------------------------------------------------

I understand each will have their benefits but which one is preferable
Like in joining the table with defining relationships we the data retrieved will be read
Only. Please help me,
Thank you for your help and time


Re: Confusion which way to use Zend DB, use Model-relationship or SQL select statements

by Dario Zamuner :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

I use to put all my queries in the model class. A typical example

class Cantiere extends Zend_Db_Table_Abstract
{
        protected $_name = 'cantiere';

        protected $_primary = 'id_cantiere';
       
        public function getFirstConstructionSite()
        {
                $query = $this->_db->select()->from($this->_name, array('id_cantiere'))->order('nome_cantiere')->limit(1);
                return $this->_db->fetchOne($query);
        }
}

and then in the controller

$this->_constructionSites = new Cantiere();
$result = $this->_constructionSites->getFirstConstructionSite();



fossil wrote:
Hi all I am confused,
Which is the better way to use zend db, is it by writing pure own sql queries
And use in select statements)as in PART A below) or use models of tables and join with
Given model relationships (PART B below)
------------------------------------------------------------------
Example PART A
------------------------------------------------------------------
$select = $db->select()
    ->from(array('p' => 'products'),
        array('product_id'))
    ->join(array('l' => 'line_items'),
        'p.product_id = l.product_id',
        array('line_items_per_product' => 'COUNT(*)'))
    ->group('p.product_id')
    ->having('line_items_per_product > 10');

------------------------------------------------------------------
<?php
$db = Zend_Db::factory( ...options... );
$select = $db->select();

------------------------------------------------------------------
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';

$result = $db->fetchAll($sql, 2);
------------------------------------------------------------------

////////////////          OR          /////////////////////

------------------------------------------------------------------
Example PART B
------------------------------------------------------------------
class Products extends Zend_Db_Table_Abstract
{
    protected $_name            = 'products';
    protected $_dependentTables = array('BugsProducts');
}

class Bugs extends Zend_Db_Table_Abstract
{
    protected $_name            = 'bugs';

    protected $_dependentTables = array('BugsProducts');

    protected $_referenceMap    = array(
        'Reporter' => array(
            'columns'           => 'reported_by',
            'refTableClass'     => 'Accounts',
            'refColumns'        => 'account_name'
        ),

--------------------------------------------------------------------------------
<?php
$bugs = new Bugs();
$row = $bugs->fetchRow('bug_id = 1');
------------------------------------------------------------------

I understand each will have their benefits but which one is preferable
Like in joining the table with defining relationships we the data retrieved will be read
Only. Please help me,
Thank you for your help and time

Re: Confusion which way to use Zend DB, use Model-relationship or SQL select statements

by Neil Garb :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

I prefer to keep my zend_tables in my library/ directory, and create
models specific to the module.  A User model might have a different
functionality depending on the module, but still access the same
table.  So something like

class User
{
 protected $_userTable;
 public function __construct()
 {
  $this->_userTable = new My_Db_Table_User();
 }
 public function search ($string)
 {
  $users = $this->_user->fetchAll(array("username LIKE '%?%'" => $string));
  return $users;
 }
}

- Neil

http://codecaine.co.za/

On Wed, Jul 2, 2008 at 1:41 AM, Shandar <beachprauti@...> wrote:

>
> I use to put all my queries in the model class. A typical example
>
> class Cantiere extends Zend_Db_Table_Abstract
> {
>        protected $_name = 'cantiere';
>
>        protected $_primary = 'id_cantiere';
>
>        public function getFirstConstructionSite()
>        {
>                $query = $this->_db->select()->from($this->_name,
> array('id_cantiere'))->order('nome_cantiere')->limit(1);
>                return $this->_db->fetchOne($query);
>        }
> }
>
> and then in the controller
>
> $this->_constructionSites = new Cantiere();
> $result = $this->_constructionSites->getFirstConstructionSite();
>
>
>
>
> fossil wrote:
>>
>> Hi all I am confused,
>> Which is the better way to use zend db, is it by writing pure own sql
>> queries
>> And use in select statements)as in PART A below) or use models of tables
>> and join with
>> Given model relationships (PART B below)
>> ------------------------------------------------------------------
>> Example PART A
>> ------------------------------------------------------------------
>> $select = $db->select()
>>     ->from(array('p' => 'products'),
>>         array('product_id'))
>>     ->join(array('l' => 'line_items'),
>>         'p.product_id = l.product_id',
>>         array('line_items_per_product' => 'COUNT(*)'))
>>     ->group('p.product_id')
>>     ->having('line_items_per_product > 10');
>>
>> ------------------------------------------------------------------
>> <?php
>> $db = Zend_Db::factory( ...options... );
>> $select = $db->select();
>>
>> ------------------------------------------------------------------
>> $sql = 'SELECT * FROM bugs WHERE bug_id = ?';
>>
>> $result = $db->fetchAll($sql, 2);
>> ------------------------------------------------------------------
>>
>> ////////////////          OR          /////////////////////
>>
>> ------------------------------------------------------------------
>> Example PART B
>> ------------------------------------------------------------------
>> class Products extends Zend_Db_Table_Abstract
>> {
>>     protected $_name            = 'products';
>>     protected $_dependentTables = array('BugsProducts');
>> }
>>
>> class Bugs extends Zend_Db_Table_Abstract
>> {
>>     protected $_name            = 'bugs';
>>
>>     protected $_dependentTables = array('BugsProducts');
>>
>>     protected $_referenceMap    = array(
>>         'Reporter' => array(
>>             'columns'           => 'reported_by',
>>             'refTableClass'     => 'Accounts',
>>             'refColumns'        => 'account_name'
>>         ),
>>
>> --------------------------------------------------------------------------------
>> <?php
>> $bugs = new Bugs();
>> $row = $bugs->fetchRow('bug_id = 1');
>> ------------------------------------------------------------------
>>
>> I understand each will have their benefits but which one is preferable
>> Like in joining the table with defining relationships we the data
>> retrieved will be read
>> Only. Please help me,
>> Thank you for your help and time
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Confusion-which-way-to-use-Zend-DB%2C-use-Model-relationship-or-SQL-select-statements-tp18167471p18227536.html
> Sent from the Zend DB mailing list archive at Nabble.com.
>
>

Parent Message unknown Re: Confusion which way to use Zend DB, use Model-relationship or SQL select statements

by Mike Ventimiglia :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Re: [fw-db] Confusion which way to use Zend DB, use Model-relationship or SQL select statements

I've set up my with a structure similar to this:

Directory structure:

/models/Category.php
/models/Category/Table.php
/models/Category/Row.php

class Category
{
 protected $_table;
 protected $_row;

 public function __construct
 {
  $this->_table = new Category_Table;
  $this->_row   = new Category_Row;
 }

 public function getAll()
 {
  return $this->_table->fetchAll();
 }
}

Most often ill return the rowset object to the controller, which I can call the toArray() method on if need be. 

I also seperate any other models related to category.

/models/Category/Metadata.php
/models/Category/Metadata/Table.php
/models/Category/Metadata/Row.php

class Category_Metadata extends Category {...}
class Category_Metadata_Table extends Zend_Db_Table_Abstract { ... }

Helps me keep it clean. Any insight into this approach is welcome.

mv

Sent via BlackBerry

----- Original Message -----
From: Shandar <beachprauti@...>
To: fw-db@... <fw-db@...>
Sent: Tue Jul 01 19:41:13 2008
Subject: Re: [fw-db] Confusion which way to use Zend DB, use Model-relationship or SQL select statements


I use to put all my queries in the model class. A typical example

class Cantiere extends Zend_Db_Table_Abstract
{
        protected $_name = 'cantiere';

        protected $_primary = 'id_cantiere';
       
        public function getFirstConstructionSite()
        {
                $query = $this->_db->select()->from($this->_name,
array('id_cantiere'))->order('nome_cantiere')->limit(1);
                return $this->_db->fetchOne($query);
        }              
}

and then in the controller

$this->_constructionSites = new Cantiere();
$result = $this->_constructionSites->getFirstConstructionSite();




fossil wrote:
>
> Hi all I am confused,
> Which is the better way to use zend db, is it by writing pure own sql
> queries
> And use in select statements)as in PART A below) or use models of tables
> and join with
> Given model relationships (PART B below)
> ------------------------------------------------------------------
> Example PART A
> ------------------------------------------------------------------
> $select = $db->select()
>     ->from(array('p' => 'products'),
>         array('product_id'))
>     ->join(array('l' => 'line_items'),
>         'p.product_id = l.product_id',
>         array('line_items_per_product' => 'COUNT(*)'))
>     ->group('p.product_id')
>     ->having('line_items_per_product > 10');
>
> ------------------------------------------------------------------
> <?php
> $db = Zend_Db::factory( ...options... );
> $select = $db->select();
>
> ------------------------------------------------------------------
> $sql = 'SELECT * FROM bugs WHERE bug_id = ?';
>
> $result = $db->fetchAll($sql, 2);
> ------------------------------------------------------------------
>
> ////////////////          OR          /////////////////////
>
> ------------------------------------------------------------------
> Example PART B
> ------------------------------------------------------------------
> class Products extends Zend_Db_Table_Abstract
> {
>     protected $_name            = 'products';
>     protected $_dependentTables = array('BugsProducts');
> }
>
> class Bugs extends Zend_Db_Table_Abstract
> {
>     protected $_name            = 'bugs';
>
>     protected $_dependentTables = array('BugsProducts');
>
>     protected $_referenceMap    = array(
>         'Reporter' => array(
>             'columns'           => 'reported_by',
>             'refTableClass'     => 'Accounts',
>             'refColumns'        => 'account_name'
>         ),
>
> --------------------------------------------------------------------------------
> <?php
> $bugs = new Bugs();
> $row = $bugs->fetchRow('bug_id = 1');
> ------------------------------------------------------------------
>
> I understand each will have their benefits but which one is preferable
> Like in joining the table with defining relationships we the data
> retrieved will be read
> Only. Please help me,
> Thank you for your help and time
>
>
>

--
View this message in context: http://www.nabble.com/Confusion-which-way-to-use-Zend-DB%2C-use-Model-relationship-or-SQL-select-statements-tp18167471p18227536.html
Sent from the Zend DB mailing list archive at Nabble.com.

LightInTheBox - Buy quality products at wholesale price
Free Forum Powered by Nabble Forum Help