如何使用query()方法获取联接表的数据 [英] How to get data of joined table using query() method

问题描述

如果我使用query()，则在获取联接表的数据时遇到问题.我没有获得产品表的数据，如何在不使用Active Record的情况下使用query()解决此问题?这是我的数据库表结构

I have an issue with getting data of the joined table if I use query(). I did not get the data of product table, how can I solve this using query() without using Active record? Here is my db table structure

        category table
        +--------------------------+
        | cId | added  | category  |       
        +-----+--------+-----------+
        |  1  | 1.2.20 |  PC       |
        |  2  | 1.7.20 | electron  |
        +-----+--------+-----------+

        product table
        +--------------------------+
        | id  |  cId   |  cost     |       
        +-----+--------+-----------+
        |  1  |   1    |  3000     |
        |  1  |   2    |  9000     |
        +-----+--------+-----------+

我的模特

    protected $table = 'category';

    public function showProduct()
    {
        $sql = "SELECT 
                     category.*, COALESCE(SUM(product.cost),0) as price 
                FROM category 
                JOIN product 
                ON product.cId = category.cId
                GROUP BY category.cId
            ";
        $this->db->query($sql);
        return $this;
    }

我的控制器

    public function index()
    {
            $result = $this->model->showProduct();
            echo "<pre>";
            print_r($result->asObject()->paginate(1));  
            
            //pagination
            $pager = $this->model->showProduct()->pager->links();
    }

我得到的结果

    Array
    (
        [0] => stdClass Object
            (
                [cId] => 1
                [added] => 1.2.20
                [category] => PC
            )

        [1] => stdClass Object
            (
                [cId] => 2
                [added] => 1.7.20
                [category] => electron
            ),

    )

推荐答案

我通过在函数中设置此表属性找到了解决方案.

I found solution to this by setting this table property within a function.

型号

$protected $table = array("default bd table here");

public function showProduct()
{
    $this->table = array('category');
    
    $sql = "SELECT 
                 category.*, COALESCE(SUM(product.cost),0) as price 
            FROM category 
            JOIN product 
            ON product.cId = category.cId
            GROUP BY category.cId
        ";
    $this->db->query($sql);
    return $this;
}

我的控制器

public function index()
{
        $result = $this->model->showProduct();
        
        //pagination
        $pager = $this->model->showProduct()->pager->links();
}

这篇关于如何使用query()方法获取联接表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！