Inner join in datamapper codeigniter

Question

I have two table products and categories, I want to get all the products with their category name.

Product Table(products)

id (auto_increment) | Name(varchar) | category_id(int) | price(decimal)  | user_id(int)

Category Table(categories)

 id(auto_increment) | name(varchar)

Required Query : SELECT * FROM products p INNER JOIN categories c ON (p.category_id = c.id) WHERE p.user_id = 1

Model 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();
}
}

Model category.php

class Category extends DataMapper {
    var $table = 'categories';
    var $has_many = array('product');
}

Controller(users)

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);
}

}

Answer 1

Datamapper ORM for CodeIgniter does not seem to have the ability to do INNER JOIN, and defaults to LEFT OUTER JOIN.

Therefore I would use ActiveRecord for your INNER JOIN query.

$result_set = $this->db->join('categories', 'categories.id = products.category_id')->where('user_id = 1')->get('products')->result_array();

Source: I use DataMapper ORM in my job.