Codeigniter: Models questions!

Question

Hi... i just got this doubt about models...

This is my first 100% MVC project (kind of)... my doubt is...when ill do a join, union or whatever function with more than one table... how i do this? use the same model file of the "main table" or i need to create a new model file using the two or whatever tables??

Tkz... Roberto!

Answer 1

I'd like to share my approach for this.

First, I made my own MY_Model class that have get_detail, get_total, get_list, get_all, insert, update, and delete method. I put this class in the folder system/application/libraries folder. This class extends CI's Model class. All related query in this class using $this->tablename. Then the actual model class will need only this code to make it work:

class Product_model extends MY_Model {

  function Product_model()
  {
    parent::MY_Model();
    $this->tablename = 'product';
  }

}

When I need join 2 tables or more, I put the code in the main table's model. Example: I have product, category, and user table. If I need to get the product, with category name and user name who insert the data, then the 'main' table will be product. I will have this method inside Product_model:

function get_list_joined($start=0, $item_num=10, $condition='', $order_by='')
{
  //do query for product, left join to category and user table
  //return result
}

Using this approach, I will still have get_list function that only return columns from product table, and also have get_list_joined function that return column from product table, category table, and user table.

It's important to determine main class first, to avoid having multiple methods that do same things.

To do join, database class included in CI is sufficient. But I prefer to use AdoDB as database library. It's offer more database support than CI's. But for start, and you only use mysql, then CI's database is enough. Learn it one at a time.

Answer 2

You can do it however you like. A model does not have to be limited to a single table, you can JOIN, UNION and UPDATE whatever table you like from wherever.

To further Donny Kurnia's suggestions, a very good existing MY_Model has already been written by Jamie Rumbleow (with a few contributions from myself ofc) which contains several get, count, insert, etc methods. These allow you to do most basic CRUD requirements simple by creating an empty model that extends from MY_Model.

That is great for one-table get/insert/delete but when it comes to creating methods that require joining, the best thing to do is just write them yourself.

When joining tables you will need to set WHERE's based on table names/aliases and you will find conflicting field names if you are not careful, meaning that creating a general solution to manage all joins queries from MY_Model is either going to be VERY difficult or just a mess.

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Tickets_m extends MY_Model
{
    // Basic get, insert, delete stuff handled in MY_Model

    function get_client_tickets($category_slug = '')
    {
        $this->db->select('t.*')
            ->select('c.title as category_title, c.slug as category_slug')
            ->select('p.name as priority_name')
            ->join('categories c', 'c.id = t.category_id')
            ->join('priorities p', 'p.id = t.priority_id')
            ->from('tickets t')
            ->where('c.client_id', CLIENT_ID);

        if($category_slug)
        {
            $this->db->where('c.slug', $category_slug);
        }

        return $this->db->get()->result();
    }

    function get_client_ticket($id)
    {
        $this->db->select('t.*')
            ->select('c.title as category_title, c.slug as category_slug')
            ->select('p.name as priority_name')
            ->join('categories c', 'c.id = t.category_id')
            ->join('priorities p', 'p.id = t.priority_id')
            ->from('tickets t')
            ->where('c.client_id', CLIENT_ID)
            ->where('t.id', $id);

        return $this->db->get()->row();
    }

    function insert($ticket)
    {
        $this->load->helper('date');

        $ticket['created_on'] = now();

        return parent::insert($ticket);
    }

}

Here is an example of a very simple model I am working with at the moment which shows me combining the use of MY_Model for get, get_by, insert, update with some custom methods which have joins and extra data in them.

You can also see me overloading insert() to automatically add in the created date without needing to set it in each controller that may be creating or updating the ticket.

If you need even more magic auto-matic joinery madness, try ORM with something like Doctrine or DataMapper