Codeigniter SQL Join with multiple ON clauses?

Question

This is the SQL query displayed by codeigniter:

SELECT * FROM (status_updates) JOIN friend_connections ON (( status_updates.userid=friend_connections.userid) OR (status_updates.userid=friend_connections.friend_id)) WHERE status_updates.enabled = "1" AND friend_connections.enabled = "1" ORDER BY status_updates.date desc, status_updates.time desc LIMIT 20

The Codeigniter commands i used were:

$this->db->from('status_updates');
$this->db->order_by("status_updates.date", "desc");
$this->db->order_by("status_updates.time", "desc");
$this->db->limit(20);
$this->db->join('status_updates', '((status_updates.userid=friend_connections.userid) OR (status_updates.userid=friend_connections.friend_id))');

What i'm trying to do is select everything in two tables, status_updates and friend_connections. status_updates has the one column userid which i want to match with either userid or friend_id from friend_connections. Can someone tell me how i can do this please?

Also i noticed that codeigniter removes the two opening parentheses after the ON command in the above sql query.

Sorry, but i've now been able to get it working. Instead of using the codeigniter sql commands, i wrote it manually. '$sql = "SELECT * FROM (status_updates) JOIN friend_connections ON ((status_updates.userid=friend_connections.userid) OR (status_updates.userid=friend_connections.friend_id)) WHERE status_updates.enabled = "1" AND friend_connections.enabled = "1" ORDER BY status_updates.date desc, status_updates.time desc LIMIT 20"; $status = $this->db->query($sql);' — Bolu
hi Bolu, can you add this as an answer and then accept that answer so it will be taken out of the "unanswered" questions page? thanks, — tim peterson

PaulSkinner PaulSkinner · Accepted Answer · 2012-05-18T09:59:37

Copy of your own answer in order to remove this from the unanswered stack:

$sql = "SELECT * FROM (status_updates) JOIN friend_connections ON ((status_updates.userid=friend_connections.userid) OR (status_updates.userid=friend_connections.friend_id)) WHERE status_updates.enabled = "1" AND friend_connections.enabled = "1" ORDER BY status_updates.date desc, status_updates.time desc LIMIT 20";
$status = $this->db->query($sql);

Please don't upvote. It's not my answer.

Codeigniter SQL Join with multiple ON clauses?

1 Answers