Full Outer Join in CodeIgniter with Active Record

Question

I have an existing web application where I'm returning user details from table 'users' and a users test results from table 'programme_results'.

(users)

|email|client_id|
|[email protected]|1|
|[email protected]|2|
|[email protected]|1|

(programme_results)

|email|client_id|prog_id|
|[email protected]|1|3|
|[email protected]|1|3|
|[email protected]|1|3|

Both tables have an 'email' column and a 'client_id' column in both tables and then a 'prog_id' column in the results table. I need to now return all rows from 'both' tables for a specific client and programme (prog_id) regardless of whether there is a match on the email column, however if there 'is' a match then the values should be aligned.

I think this would be what's typically referred to as a 'full outer join' but I don't see any mention in the docs of this being supported by CodeIgniter Active Record. :(

Currently I have my active record query roughly a follows:

$this->db->select('u.email AS user_email, u.client_id AS user_client_id, r.email AS result_email, r.client_id AS result_client_id');
$this->db->from('programmes_results r');
$this->db->join('users u', 'u.email=r.email', 'left outer');
$this->db->where('r.client_id', 1);
$this->db->where('r.prog_id', 3);
$query = $this->db->get();

Which generates:

SELECT u.email AS user_email, u.client_id AS user_client_id, r.email AS result_email, r.client_id AS result_client_id FROM programmes_results r LEFT OUTER JOIN users u ON u.email=r.email WHERE r.client_id=1 AND r.prog_id=3

However this gives me a result set as follows where it's matching users without taking any notice of 'their' client_id value:

|user_email|user_client_id|result_email|result_client_id|
|[email protected]|130|[email protected]|1|
|NULL|NULL|[email protected]|1|
|[email protected]|2|NULL|NULL|

Also, more importantly it's not returning values from 'users' where there isn't a matching email address in 'programmes_results'. What I want to end up with is:

|user_email|user_client_id|result_email|result_client_id|
|[email protected]|130|[email protected]|1|
|NULL|NULL|[email protected]|1|
|[email protected]|1|NULL|NULL|

:?

Did you get this working? If not, I may take a stab at it for you. — seangates

William Knauss William Knauss · Accepted Answer · 2015-11-07T03:28:46

I believe you can get the desired affect by using this:

$query = $this->db->select('u.email AS user_email, u.client_id AS user_client_id, r.email AS result_email, r.client_id AS result_client_id')
->from('programmes_results r')
->join('users u', 'u.email=r.email', 'left outer')
->where('r.client_id', 1)
->where('r.prog_id', 3)
->where('u.client_id IS NOT NULL')
->get();

Which will produce SQL equal to:

SELECT 
    u.email AS user_email, 
    u.client_id AS user_client_id, 
    r.email AS result_email, 
    r.client_id AS result_client_id 
FROM 
    programmes_results r 
LEFT OUTER JOIN 
    users u ON 
        u.email=r.email 
WHERE 
    r.client_id = 1 AND 
    r.prog_id = 3 AND
    u.client_id IS NOT NULL

Full Outer Join in CodeIgniter with Active Record

1 Answers