Join with Active Codeigniter Record

Question

Join with Active Codeigniter Record

Navigation

#1 by (1 votes)

2

I made a query that is running ok and I'm passing to the Codeigniter Active Record but it is not working because of a join that is a bit off with conventional, does anyone have any idea what it can 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 can be?

query codeigniter active-record

asked by anonymous 11.06.2014 / 15:42

1 answer

Put a decision control inside a lambda expression Retrieve Auto Increment Id DataSnap Delphi XE3

score 1 · 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;
}

Then, $table is the name of the table that relates the $cond is the condition and $type that can 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 to putting a SQL in there because by automatic 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");

Actually you already have the solution and when doing a complex SQL is the best way to execute SQL statements in CodeIgniter.