Codeigniter: MySQL where clause, and quotes

Question

Here's my query in Codeigniter:

$this->db->select('comments.created_at, comments.section_id, comments.submittedby_id, users.username, comments.text, sections.name');
$this->db->order_by('comments.created_at', 'desc');
$this->db->where('comments.submittedby_id',  'users.user_id'); 
$this->db->where('comments.section_id', 'sections.id'); 

$query = $this->db->get(array('comments', 'users', 'sections'),10);

This produces the following SQL request:

SELECT pdb_comments.created_at, pdb_comments.section_id, pdb_comments.submittedby_id, pdb_users.username, pdb_comments.text, pdb_sections.name FROM (pdb_comments, pdb_users, pdb_sections) WHERE pdb_comments.submittedby_id = 'users.user_id' AND pdb_comments.section_id = 'sections.id' ORDER BY pdb_comments.created_at desc LIMIT 10

Issue with this is that the DB prefix ('pdb_') does not get added in the WHERE clause. I can manually insert the prefix by appending "$this->db->dbprefix", but this doesn't fix the main problem -- quotes:

`pdb_comments`.`submittedby_id` = 'pdb_users.user_id'

The quotes on the right side are not accurate and generate 0 results for me. Is there any way to make Codeigniter recognize the second half of the where clause as a piece of my table? (thereby adding the DB prefix and properly placing the quotes)

Or is there another way to do this? (trying to avoid 2 joins here)

Thanks. Jon

Answer 1

Possibly a dumb question, but why don't you just write the SQL directly? The interface doesn't look like it's giving you anything but clutter.

Answer 2

Use:

$this->db->select('comments.created_at, comments.section_id, comments.submittedby_id, users.username, comments.text, sections.name');
$this->db->from('comments');
$this->db->join('users', 'comments.submittedby_id=users.user_id'); 
$this->db->join('sections', 'comments.section_id=sections.id'); 
$this->db->order_by('comments.created_at', 'desc');
$query = $this->db->get();

instead.