Search code examples
mysqlcodeignitercodeigniter-2

How to use AND in JOIN the Codeigniter way

How can I transform this MySQL query into Codeigniter query?

SELECT *
FROM categories
LEFT JOIN user_category_subscriptions ON 
    user_category_subscriptions.category_id = categories.category_id
    and user_category_subscriptions.user_id =1
Solution

  • You need to place the AND part inside Codeigniter's join()

    $query = $this->db  ->select('t1.*')
                    ->join('user_category_subscriptions t2', 't1.category_id =t2.category_id AND t2.user_id =1','left')
                    ->get('categories t1');
return ($query->num_rows())?$query->result():false;

    with this line after the query you can double-check if the query was generated correctly:

    echo $this->db->last_query();die;

    more about CI 2.x Active Record Class and alternatively CI 3.x Query Builder Class