我有两个表products
和categories
,我想获得所有产品及其类别名称。
产品表(产品)
id (auto_increment) | Name(varchar) | category_id(int) | price(decimal) | user_id(int)
分类表(类别)
id(auto_increment) | name(varchar)
必选查询: SELECT * FROM products p INNER JOIN categories c ON (p.category_id = c.id) WHERE p.user_id = 1
模型product.php
class Product extends DataMapper {
var $has_many = array('category');
var $validation = array(
'name' => array(
'label' => 'Name',
'rules' => array('required', 'trim', 'alpha_dash', 'min_length' => 3),
),
'category_id' => array(
'label' => 'Category Id',
'rules' => array('required', 'trim', 'alpha_dash'),
),
'price' => array(
'label' => 'price',
'rules' => array('required', 'trim', 'alpha_dash'),
),
'quantity' => array(
'label' => 'quantity',
'rules' => array('required', 'trim', 'alpha_dash'),
),
);
function get_product_from_userid($product_id){
$product_obj = new Product($product_id);
$product_obj->category->get();
}
}
模型category.php
class Category extends DataMapper {
var $table = 'categories';
var $has_many = array('product');
}
控制器(用户)
class Users extends CI_Controller {
function Users(){
parent::__construct();
$this->load->helper('url');
$this->load->helper('form');
}
function index(){
$product_obj = new Product();
$product_obj->user_id = 1;
$product_id = 1;
$product_obj->get_product_from_userid($product_id);
}
}
CodeIgniter的Datamapper ORM似乎没有INNER JOIN的能力,默认为LEFT OUTER JOIN。
因此我将使用ActiveRecord为您的INNER JOIN查询。
$result_set = $this->db->join('categories', 'categories.id = products.category_id')->where('user_id = 1')->get('products')->result_array();
来源:我使用DataMapper ORM在我的工作。