how to grab data from multiple tables and display in view Using Zend Framework

Question

Thanks in advance...see below the code.. i have 2 models, category and product

my product model class Admin_Model_Product extends Zend_Db_Table_Abstract {

protected $_name = 'products';
protected $_referenceMap = array(
    'category' => array(
        'columns' => array('category_id'),
        'refTableClass' => 'Admin_Model_Category',
        'refColumns' => array('id'),
        'onDelete' => self::CASCADE,
        'onUpdate' => self::RESTRICT
    )
);

}

my category model is:

class Admin_Model_Category extends Zend_Db_Table_Abstract {

protected $_name = 'categories';
protected $_dependentTables = array('Admin_Model_Product');

} in my products controller i have

class Admin_ProductsController extends Zend_Controller_Action {

public function init() {

}

public function indexAction() {
    echo '<pre>';
    $model = new Admin_Model_Product();

}

}

What i need to do is get all the products using fetchAll() method and need to get parentrow of each product and display it in my view... i can pull all the products but i dont know how to find each products parent category and bind them, is there any example source code? or any suggestion ? i need an array containg all products and parent category name ..please be quick.thanks

Bartosz Grzybowski · Answer 1 · 2012-02-20T21:48:49.623

0

Best way to accomplish this would be iterating over products result and creating array containg all categories_ids, then query the Category Model with where('category_id IN (?)', $array_of_categories_ids) and then creating an array from categories rowset with id_category => row_pairs. Then You can do this in just two queries :)

$categories_ids = array();
foreach ($products as $product)
{
     $categories_ids[ $product->category_id ] = $product->category_id; // set key to category id to avoid duplicated category' ids
}
$categories = $categoryModel->fetchAll($categoryModel->select()->where('id_category in (?)', $categories_ids)); // here u have to add check on array 'coz if empty it will thoro Query exception
// now just iterate over categories
$categories = array();
foreach ($categories as $category)
{
     $categories[ $category->id_category ] = $category;
}
// now when iterating over products u can just do $categories[ $product->category_id ] to get proper category for profuct

Anyway sorry for possible typos, wrote it on the fly ;)

edited Feb 20 '12 at 21:48

answered Feb 20 '12 at 21:27

Bartosz Grzybowski

1,149
8
18

thanks for your attention..i am new to zend and working on my first project... i can do this by using **Joins()** but i could not understand your approach...could you give my any code example ? thanks in advance.. – jugnu Feb 20 '12 at 21:38
great dear...your solution may seems a little difficult but its really goood... thanks :-) – jugnu Feb 20 '12 at 23:01
It's just simple data manipulation, ofcourse You could overwrite _Rowset class for product and create this method there and just call `$productsRowset->getCategories()` or such, but it's way more complicated than this :D – Bartosz Grzybowski Feb 20 '12 at 23:02
what if we have more tables ? for example if i have product_images,product_sizes ? how will you refector your solution ? – jugnu Feb 20 '12 at 23:03
and one thing more...if i approach your solution then why i need to define relationships ? as i am qurring data on current id's...so by doing this,do i need to define relaitions ? – jugnu Feb 20 '12 at 23:05
That depends on schema, obviously there are many ways to accomplish this, but it depends on what data You need at current time, probably I would wrap those functions in `_Rowset` classes to make clear api for retriving those values without quering database too many times. – Bartosz Grzybowski Feb 20 '12 at 23:07
Defining relations makes some things easier, like getting one product and just getting parent category, or category with products collection etc. For this problem my solution is best, but for others using Zend's builded-in relations engine is just enough. Ofcourse You don't have to define relations at all and build all queries by Yourself. – Bartosz Grzybowski Feb 20 '12 at 23:09
at this time... i want to get all products(parent category) and all dependent tables like ( product_images,product_sizes,product_colors...etc )... i have a quite clear vision after ur help... how you will deal with this situation ? i am sorry i really a new guy.. – jugnu Feb 20 '12 at 23:12
If You display a product (one row) then You can easly query others tables for more data, it also depends how much data those connected tables have, You can query DB by relation for each product's resource, like photos/sizes/etc in product view, but if You query those resources with many products use `IN` instead. – Bartosz Grzybowski Feb 20 '12 at 23:15
thats great... hope i would be able to do this...thanks for your help and attention... much appriciated :) – jugnu Feb 20 '12 at 23:17
No problem, It's just common problem to know where use `IN` instead of `JOINS` and vice versa. – Bartosz Grzybowski Feb 20 '12 at 23:18

Mouna Cheikhna · Accepted Answer · 2012-02-20T22:01:12.707

-1

try the following :

retrieve categories and their products :

$model_category = new Admin_Model_Category();
$categorySet = $model_category->fetchAll(); 
$categories = $categorySet->toArray();

$i = 0;
$results = array();
foreach($categories as $category)
   $categoryRow = $model_category->fetchRow('id =', $category->category_id)
   $products = $categoryRow->findDependentRowset('Admin_Model_Product');
   $results[$i]['parent'] = $category;
   $results[$i]['products'] = $products;
   $i++;
}

then pass it to the view:

$view->results = results;

edited Feb 20 '12 at 22:01

answered Feb 20 '12 at 21:36

Mouna Cheikhna

38,870
10
48
69

This is worst thing to do, now You are creating n queries, where n is number of products, people should avoid this solution since u can make it in just to queries. – Bartosz Grzybowski Feb 20 '12 at 21:42
** Fatal error: Call to a member function findDependentRowset() on a non-object in C:\xampp\htdocs\wnw\application\modules\admin\controllers\ProductsController.p ** – jugnu Feb 20 '12 at 21:47
i added getting a categroyRow to be able to call findDependentRowset – Mouna Cheikhna Feb 20 '12 at 22:02
i am not that expert but ur answer gave me what i wanted...thanks to you and others for helping me... :-) – jugnu Feb 20 '12 at 22:36

how to grab data from multiple tables and display in view Using Zend Framework

2 Answers2