You can get your answer from here.
Read here
MySql Query.
SELECT Connection.connected_with, Connection.date
FROM Connection
JOIN User ON User.userid = Connection.userid
WHERE Connection.userid =1
Codeigniter Active Record
$this->db->select('connected_with', 'date');
$this->db->from('Connection');
$this->db->join('User', 'User.userid' = 'Connection.userid');
$this->db->where('userid', 1);
$this->db->get();
Like you said in comment, you have two foreign keys userid
& connected_with
, you can use union to combine both query result. First query you find the connection where Connection.userid=1
. Second query you find the connection where Connection.connected_with=1
. Then combine both result.
See the code below
SELECT Connection.userid AS 'Connection'
FROM Connection
JOIN User ON User.userid = Connection.connected_with
WHERE Connection.connected_with =1
UNION
SELECT Connection.connected_with
FROM Connection
JOIN User ON User.userid = Connection.userid
WHERE Connection.userid =1
Codeigniter Active Record
// First Query
$this->db->select('connected_with', 'date');
$this->db->from('Connection');
$this->db->join('User', 'User.userid' = 'Connection.userid');
$this->db->where('userid', 1);
$query = $this->db->get();
$subQuery1 = $this->db->_compile_select();
$this->db->_reset_select();
// Second Query
$this->db->select('userid', 'date');
$this->db->from('Connection');
$this->db->join('User', 'User.userid' = 'Connection.connected_with);
$this->db->where('connected_with', 1);
$query = $this->db->get();
$subQuery2 = $this->db->_compile_select();
$this->db->_reset_select();
// Union
$this->db->from("($subQuery1 UNION $subQuery2)");
$this->db->get();
Output
+--------------------------+
| Connection for User ID 1 |
+--------------------------+
| 4 |
| 2 |
| 3 |
+--------------------------+