Get data from 2 tables joint in abother table

[absmugz]

Hi,
I have a simple database which uses joins as follows :

Table 1 contains portfolio items:
Portfolio_items
portfolio_id
name
description

Table 2 contains portfolio categories:
Categories
categories_id
name

Table 3 contains the portfolio categories join ids:
Portfolio_categories
portfolio_id
categories_id

So what I would like to do is get all 1 record from Portfolio_items and all the categories that 1 portfolio item is in.
Basically please point me in the right direction of how to get data from Portfolio_items with multiple and Categories where the joines are in id's in Portfolio_categories using MY_Model.

Do I have to write custom functions like :

class A_model extends MY_Model
{
public function Example()
{
Custom ActiveRecord query goes here
}
}

Can you please give me a rough example using the table structure above ? Thank you

[absmugz]

I ended up writing my own custom models and extending the CI_Model :

class Portfolio_model extends CI_Model {

function __construct()
    {
        parent::__construct();
    }

public function get_all()             
{

$this->db->select('*');
$this->db->from('portfolio_items');
$this->db->join('portfolio_categories', 'portfolio_categories.portfolio_id = portfolio_items.portfolio_id ');
$this->db->join('categories', 'categories.categories_id = portfolio_categories.categories_id');

$query = $this->db->get();
return $query->result_array();       
}

public function get_one($id)             
{

$this->db->select('*');
$this->db->from('portfolio_items');
$this->db->join('portfolio_categories', 'portfolio_categories.portfolio_id = portfolio_items.portfolio_id ');
$this->db->join('categories', 'categories.categories_id = portfolio_categories.categories_id');
$this->db->where('portfolio_items.portfolio_id', $id); 

$query = $this->db->get();
return $query->result_array();       
}

It works but its not using the efficient MY_MODEL, how do I integrate this with MY_MODEL ? So that I end up not writing too much code like above ?

[michail1982]

@absmugz you can write method

Class My_mega_model extends MY_Model {
//Add line below, if you always need join`s
protected $before_get = array('with_my_stuff');

public function with_my_stuff()
{
//here you join tables code goes here
return $this;
}
}

[absmugz]

Thanks Michail1982,
I have tested the following but getting an error

class Portfolio_model extends MY_Model {

protected $before_get = array('with_my_stuff');

public $_table = 'portfolio_items';
public $primary_key = 'portfolio_id';


public function with_my_stuff()
{
//here you join tables code goes here
$this->db->join('portfolio_categories', 'portfolio_categories.portfolio_id = portfolio_items.portfolio_id ');
$this->db->join('categories', 'categories.categories_id = portfolio_categories.categories_id');
return $this;
}

[michail1982]

check docs. before_get must accept and return $row my example explaining idea ;)

[markdave27]

If you're that familiar in creating queries, you can just create a view on your database. Then just create an instance of that view with your models and treat is a normal table. This is what works for me and what I am used to. Of course you still need to create separate model for each tables that you used on your view for the management of those table. And also, take note, you don't update and/or write on your view directly, you need to update and/or write on the table(s) that you joined together, the view will then be automatically updated because it is just pulling the records/data from your source tables.

[paulcanning]

@michail1982 your example is returning $this, not $row...

[michail1982]

@paulcanning i know)) . my example can be used in case

$this->my_mega->model->with_my_stuff()->get();/// or any other AR method

when with_my_stuff in before_get array method must receive and return some varible