Separating SQL, PHP and the view when listing categories and products in Kohana

Question

I want to migrate to Kohana with my small websites and I'm trying to separate the SQL, PHP and the view, but I've some problems with this one.

I have to tables. Every category can have multiple products.

Categories table

• id
• category

Products table

• id
• category_id
• product

This was my previous code (converted to Kohana's query builder):

     $categories = DB::select('id', 'category')
   ->from('categories')
   ->as_object()
   ->execute();

 foreach ($categories as $category)
 {
  echo '<b>'.$category->category.'</b><br />';
  $products = DB::select('product')
    ->from('products')
    ->where('category_id', '=', $category->id)
    ->as_object()
    ->execute();

  foreach($products as $product)
  {
   echo $product->product.'<br />';
  }
  echo '<hr />';
 }

I want to do the same, just in the view file I don't want to use other than echoing out the variables.

Update: I would prefer a solution without using Kohana's ORM module. Btw I'm using Kohana 3.0

Update 2:

I've accepted Lukasz's last solution, but a few modifications are needed to do exactly what I wanted to (note that this is for Kohana 3.0, while Lukasz was working with an older version):

SQL code:

$products = DB::select(array('categories.category', 'cat'), array('products.product', 'prod'))
                ->from('categories')
                ->join('products','RIGHT')
                ->on('products.category_id','category.id')
                ->as_object()
                ->execute();

Code in the view file (see comments for explanation):

     // Let's define a new variable which will hold the current category in the foreach loop
     $current_cat = '';
 //We loop through the SQL results
 foreach ($products as $product)
 {
  // We're displaying the category names only if the $current_cat differs from the category that is currently retrieved from the SQL results - this is needed for avoiding the category to be displayed multiple times
  // At the begining of the loop the $current_cat is empty, so it will display the first category name
  if($curren_cat !== $product->cat)
  {
   // We are displaying a separator between the categories and we need to do it here, because if we display it at the end of the loop it will separate multiple products in a category
   // We avoid displaying the separator at the top of the first category by an if statement
   if($current_cat !== '')
   {
    //Separator
    echo '<hr />';
   }
   // We echo out the category
   echo '<b>'.$product->cat.'</b><br />';
   // This is the point where set the $current_cat variable to the category that is currently being displayed, so if there's more than 1 product in the category the category name won't be displayed again
   $current_cat = $product->cat;
  }
  // We echo out the products
  echo $product->prod.'<br />';
 }

I hope this was helpful to others as well, if anybody has a better solution go on, share it!

Answer 1

This should work:

$categories = ORM::factory('category')->find_all();

foreach ($categories as $category)
{
    echo '<b>'.$category->category.'</b><br />';
    $products = ORM::factory('product')->where('category_id',$category->id)->find();

    foreach($products as $product)
    {
        echo $product->product.'<br />';
    }
    echo '<hr />';
}

I assume that you created models for each table? If not, read here.

Better if you separate data and view layers. And create relations between categories and products in Model files. Then in controller you should call only this:

$categories = ORM::factory('category')->find_all();
$view = new View('yourView');
$viem->categories = $categories;

And in view file:

foreach ($categories as $category)
{
    echo '<b>'.$category->category.'</b><br />';

    foreach($category->products as $product)
    {
        echo $product->product.'<br />';
    }
    echo '<hr />';
}

You will not have to call 2nd query for every row. Kohana ORM will do it for you. All you have to do is to create appropriate models:

class Category_Model extends ORM {
    protected $has_many = array('products');
}

class Product_Model extends ORM {
    protected $belongs_to = array('category');
}

Another approach: you can join tables categories and products

$products = DB::select('categories.category AS cat','products.product AS prod')
                    ->from('categories')
                    ->join('products','products.category_id','category.id')
                    ->as_object()
                    ->execute();

And then:

$current_cat = '';

foreach($products as $product)
{
    if($current_cat != $products->cat)
    {
        echo '<b>'.$products->cat.'</b><br />';
        $current_cat = $products->cat;
    }

    echo $product->prod.'<br />';
}

Answer 2

Just a quick thought:

When using the ORM-approach, I think you could achieve the same thing as a join-query using the with() method:

$products = ORM::factory('product')
  ->with('category')
  ->find_all();
$view = new View('yourView');
$viem->products = $products;

I haven't had enough time to mess around with it, but this is what I would try.