Join with Codeigniter Active Record

Question

I made a query that is running ok and I am passing to the Active Record of Codeigniter but it is not working because of a Join that is a bit out with conventional, does anyone have any idea what might be?

$query = $this->db->query("
    SELECT count(rl.id) as total, rl.dt_validate
    FROM respostas r
    JOIN respostas_log rl 
        ON rl.id = (select max(rl2.id) from respostas_log rl2 where rl2.respostas_id = r.id)
    GROUP BY rl.dt_validate
    ");

And with Active Record does not run....

$query = $this->db
        ->select('count(rl.id) as total, rl.dt_validate')
        ->from('respostas r')
        ->join('respostas_log rl', "rl.id = (select max(rl2.id) from respostas_log rl2 where rl2.respostas_id = mr.id)")
        ->group_by('rl.dt_validate')
        ->get();

Does anyone have any idea what it might be?

Answer 1

Explanation:

The method is this:

public function join($table, $cond, $type = '')
{
    if ($type != '')
    {
        $type = strtoupper(trim($type));

        if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER')))
        {
            $type = '';
        }
        else
        {
            $type .= ' ';
        }
    }
    // Extract any aliases that might exist.  We use this information
    // in the _protect_identifiers to know whether to add a table prefix
    $this->_track_aliases($table);
    // Strip apart the condition and protect the identifiers
    if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))
    {
        $match[1] = $this->_protect_identifiers($match[1]);
        $match[3] = $this->_protect_identifiers($match[3]);

        $cond = $match[1].$match[2].$match[3];
    }
    // Assemble the JOIN statement
    $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond;
    $this->ar_join[] = $join;
    if ($this->ar_caching === TRUE)
    {
        $this->ar_cache_join[] = $join;
        $this->ar_cache_exists[] = 'join';
    }
    return $this;
}

So, $table is the name of the table relating to $cond is the condition and the $type which may be some of these 'LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'

Example

$this->db->from('cliente')
    ->join('telefone', 'cliente.id = telefone.clienteid', 'left')
    ->get()
    ->result();

This is the format it is limited in putting an SQL there because by auto it puts an escape (`), this SQL being generated by it:

SELECT * FROM (`cliente`) LEFT JOIN `telefone` ON `cliente`.`id` = `telefone`.`clienteid`

Solution:

$query = $this->db->query("SELECT count(rl.id) as total, rl.dt_validate FROM respostas r JOIN respostas_log rl ON rl.id = (select max(rl2.id) from respostas_log rl2 where rl2.respostas_id = r.id) GROUP BY rl.dt_validate");

In fact you already have the solution and when you make a complex SQL is the best way to execute SQL statements in Codeigniter.