Active record is a neet concept but sometimes I find it difficult to get more complicated queries to work. I find this is at least one place the CI docs are lacking.
Anyway,
This is the sql I wrote. It returns the expected results of quests not yet completed by the user that are unlocked and within the users level requirements:
SELECT writing_quests . *
FROM `writing_quests`
LEFT OUTER JOIN members_quests_completed ON members_quests_completed.quest_id = writing_quests.id
LEFT OUTER JOIN members ON members.id = $user_id
WHERE writing_quests.unlocked =1
AND writing_quests.level_required <= $userlevel
AND members_quests_completed.user_id IS NULL
This is the codeigniter active record query, it returns all quests that are unlocked and within the users level requirement:
$this->db->select('writing_quests.*');
$this->db->from('writing_quests');
$this->db->join('members_quests_completed', 'members_quests_completed.quest_id = writing_quests.id', 'left outer');
$this->db->join('members', "members.id = $user_id", 'left outer');
$this->db->where('writing_quests.unlock', 1);
$this->db->where('writing_quests.level_required <=', $userlevel);
$this->db->where('members_quests_completed.user_id is null', null, true);
I'm guessing there is something wrong with the way I am asking for Nulls. To be thorough, I figured I'd include everything.
