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);
Produces SQL (spacing added for readability):
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
The issue is that the database 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 database prefix, and properly placing the quotes by avoiding two joins? Is there another way to do this?
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.