Advanced MySQL: Active Record Query not working properly?

Advanced MySQL: Active Record Query not working properly? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Advanced MySQL: Active Record Query not working properly? (/showthread.php?tid=8991)

Advanced MySQL: Active Record Query not working properly? - El Forum - 06-07-2008

[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!

Advanced MySQL: Active Record Query not working properly? - El Forum - 06-07-2008

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

Advanced MySQL: Active Record Query not working properly? - El Forum - 06-07-2008

[eluser]MercuryLime[/eluser]
The error is that the final line ($this->db->get() Wink

falsely returns 0 results when it should match data as first query does.

Advanced MySQL: Active Record Query not working properly? - El Forum - 06-07-2008

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

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

to:

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

Advanced MySQL: Active Record Query not working properly? - El Forum - 06-07-2008

[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-guide/database/active_record.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.

Advanced MySQL: Active Record Query not working properly? - El Forum - 06-07-2008

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

Advanced MySQL: Active Record Query not working properly? - El Forum - 06-08-2008

[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();