我想将我的小型网站迁移到 Kohana，我正在尝试分离 SQL、PHP 和 View ，但我在这方面遇到了一些问题。

我要表。每个类别可以有多个产品。

类别表

• 编号
• 类别

产品表

• 编号
• 类别编号
• 产品

这是我以前的代码(转换为 Kohana 的查询生成器):

        $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 />'; 
    } 

我想做同样的事情，只是在 View 文件中除了回显变量我不想使用。

更新: 我更喜欢不使用 Kohana 的 ORM 模块的解决方案。 顺便说一句，我正在使用 Kohana 3.0

更新 2:

我已经接受了 Lukasz 的最后一个解决方案，但需要进行一些修改才能完全按照我的意愿进行(请注意，这是针对 Kohana 3.0，而 Lukasz 使用的是旧版本):

SQL 代码:

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

View 文件中的代码(解释见注释):

        // 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 />'; 
    } 

我希望这对其他人也有帮助，如果有人有更好的解决方案，请继续分享!

请您参考如下方法:

这应该有效:

$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 />'; 
} 

我假设您为每个表都创建了模型？如果没有，请阅读 here .

更好 如果您分离数据层和 View 层。并在模型文件中创建类别和产品之间的关系。然后在 Controller 中你应该只调用这个:

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

在 View 文件中:

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

您不必为每一行调用第二个查询。 Kohana ORM 会为你做这件事。您所要做的就是创建合适的模型:

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

另一种方法:您可以加入表格类别和产品

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

然后:

$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 />'; 
}