[SOLVED] Convert SQL containing multiple JOINs into CodeIgniter's active record syntax

Convert SQL containing multiple JOINs into CodeIgniter's active record syntax

How do we write this multiple inner join and left join query as an active record script in CodeIgniter?

SELECT
    suppliers.*,
    category.strCategory,
    category_1.strCategory AS strParent,
    tblcitylist.city_name
FROM ((
    suppliers
INNER JOIN
    category ON suppliers.intCat=Category.intId)
INNER JOIN
    tblcitylist ON suppliers.intCity=tblcitylist.city_id)
LEFT JOIN
    category AS category_1 ON category.intParent=Category_1.in Id
WHERE
    status='y';

I have three tables: suppliers, tblcitylist and category. I want to fetch data for suppliers having intCat and intCity resulting with data of suppliers table with city name(city_name) and category name (strCategory).

Solution

Perhaps this is what you're after...

$query = $this->db->select('suppliers.*, category.strCategory, category_1.strCategory AS strParent, tblcitylist.city_name')
            ->from('suppliers')
            ->join('category',                  'suppliers.intCat=Category.intId',          'inner')
            ->join('tblcitylist',               'suppliers.intCity=tblcitylist.city_id',    'inner')
            ->join('category as `category_1',   'category.intParent=Category_1.intId',      'left')
            ->where('status','y')
                ->get();

CI DB Docs: https://www.codeigniter.com/user_guide/database/index.html