使用codeigniter选择多个表 - multiple table select with codeigniter

multiple table select with codeigniter

本文关键字：选择 codeigniter 使用 | 更新日期: 2023-09-27

我有一个'order'表，其中包含订单详细信息，如id，日期等和一个'order_products'表，其中包含(order_id,product_id)

我想创建一个SELECT返回一个数组里面包含所有产品id的数组

例子:$数组= (id、日期…,阵列产品(id1, id2…))

这是我的代码:

public function get_order_single($id)
{
     $query = $this->db->select( '
                orders.id,
                orders.customer_id AS customerID,
                orders.date,
                orders.payment_method AS paymentMethod,
                orders.total_price AS totalPrice,
                orders.delivery_address AS address,
                orders.modified,
                orders.created')
                ->from( 'orders' )
                ->where('id',$id)
                ->get()
                ->result( 'array' );    
    return $query[0];
}

我需要添加什么才能从第二个表中获得所有信息?

public function get_order_single($id)
{
    // Get your order
    $order = $this->db->select('
            orders.id,
            orders.customer_id AS customerID,
            orders.date,
            orders.payment_method AS paymentMethod,
            orders.total_price AS totalPrice,
            orders.delivery_address AS address,
            orders.modified,
            orders.created')
        ->from('orders')
        ->where('id', $id)
        ->get()
        ->row();
    // If order is exists, lets find products for it
    if ($order) {
        // Here we do a join also to your pivot table
        $order->products = $this->db
            ->where_in('order_products.order_id', $order->id)
            ->join('order_products', 'order_products.product_id = products.id', 'left')
            ->get('products')
            ->result();
    }
    return $order;
}

试着这样做:

$this->db->select('name, value'); // SELECT which columns you want
$this->db->join('attribute', 'attribute.id = product_attribute.attribute_id', 'left'); // JOIN 1st table
$this->db->join('attribute_value', 'attribute_value.attribute_id = product_attribute.attribute_id', 'left'); // JOIN 2nd table
$this->db->where('product_id', $id); // CONDITION
$query = $this->db->get('product_attribute'); // FROM clause
return $query->result();