[SOLVED] Convert SQL containing subquery as a JOINed derived table to CodeIgniter's active record

Convert SQL containing subquery as a JOINed derived table to CodeIgniter's active record

I'm trying to convert this SQL query into a codeigniter query

SELECT 
  uploads.EMAIL
FROM
  uploads 
JOIN (
    SELECT EMAIL, COUNT(*) as num FROM uploads GROUP BY EMAIL
  ) c ON uploads.EMAIL = c.EMAIL
ORDER BY 
  c.num DESC, 
  EMAIL ASC

Solution

I am not sure why you can't figure this out yourself using the active record documentation, but:

$this->db->select('uploads.EMAIL');
$this->db->from('uploads');
$this->db->join('(SELECT EMAIL, COUNT(*) as num FROM uploads GROUP BY EMAIL) c','uploads.EMAIL = c.EMAIL','',FALSE);
$this->db->order_by('c.num desc, uploads.EMAIL asc');

and then

$query = $this->db->get();

FYI, passing FALSE as the fourth parameter to the db->join() method will cause it not to escape the statement, so you should be careful if you're going to take external variables. This is, until CodeIgniter 3, the only way to do subqueries with active record without extending the active record class to add them.