Advanced MySQL: Active Record Query not working properly?

[eluser]MercuryLime[/eluser]
Do you have an idea how to write:

Code:

$query = $this->db->query('SELECT c.title, c.description, r.completion_notes, r.outcome FROM challenges AS c, records AS r WHERE c.id = r.challenge_id AND r.user_id = ' . $user_id . ' AND r.outcome = "ip" LIMIT 100');

as an Active Record Query?

I tried the following, but it returned 0 results.

Code:

$this->db->select('c.title, c.description, r.completion_notes, r.outcome');
        $this->db->from('challenges AS c, records AS r');
        $this->db->where(array('r.challenge_id' => 'c.id', 'r.user_id' => $user_id, 'r.outcome' => 'ip'));
        $this->db->limit(100, 0);
        $query = $this->db->get();

Help would be appreciated!

[eluser]stuffradio[/eluser]
What is the error?

[eluser]MercuryLime[/eluser]
The error is that the final line ($this->db->get() falsely returns 0 results when it should match data as first query does.

[eluser]stuffradio[/eluser]
What if you changed

Code:

$this->db->limit(100, 0);

to:

Code:

$this->db->limit(0, 100);

[eluser]MercuryLime[/eluser]
I am debugging another part of my app right now so I can't test, but according to http://ellislab.com/codeigniter/user-gui...ecord.html the second (optional) parameter is definitely the offset, so that seems like it wouldn't work.

Thanks for the suggestion though, and I will try it when I figure out how to fix the current bugs.

EDIT: No, I removed the second parameter (0) from the LIMIT part and it still didn't work.

I am doing fine just using queries but it's not as ideal as using the Active Record class. Oh well, at least it works.

[eluser]Michael Wales[/eluser]
Turn on Profiling to review the exact query CI's Active Record class is generating.

[eluser]Seppo[/eluser]
The query is wron in AR when you do this 'r.challenge_id' => 'c.id' it is using c.id as a string, not as a field...

Code:

$this->db->select('c.title, c.description, r.completion_notes, r.outcome');
$this->db->from('challenges AS c');
$this->db->join('records AS r', 'r.challenge_id = c.id');
$this->db->where(array('r.user_id' => $user_id, 'r.outcome' => 'ip'));
$this->db->limit(100, 0);
$query = $this->db->get();