Transform SQL Query to PHP Activerecord

Question

I am trying to transform my sql query in a phpactiverecord query. But it doesn't works. I get this error message:

    Fatal error: Uncaught exception 'ActiveRecord\DatabaseException' with message '42S02, 1109, 
    Unknown table 'c' in field list' in ..\activerecord\lib\Connection.php:277 
    Stack trace: #0 ..\inc\activerecord\lib\Table.php(208): ActiveRecord\Connection->query('SELECT distinct...', NULL) 
    #1 ..\inc\activerecord\lib\Model.php(1481): ActiveRecord\Table->find_by_sql('SELECT distinct...', NULL, true) 
    #2 ..\zexport.php(100): ActiveRecord\Model::find_by_sql('SELECT distinct...') 
    #3 {main} thrown in ..\inc\activerecord\lib\Connection.php on line 277

Here the try i have made:

    $totalvalues = RefTable::find_by_sql("SELECT distinct c.code, p.gestion");
    for ($i = 1; $i <= count($steps); $i++) {
        $totalvalues.=RefTable::find_by_sql(",(SELECT IFNULL(ROUND(SUM(c".$i.".`delta`),1),'')
                                FROM charge c".$i.", totalgestion p".$i."
                                WHERE c".$i.".code = p.code
                                AND c".$i.".steps = ".$i."
                                AND c".$i.".actif = 1) as delta".$i.",'' as comments".$i.",
                         (SELECT IFNULL(ROUND(SUM(c".$i.".`values`),1),'')
                                FROM charges c".$i.", totalgestion p".$i."
                                WHERE c".$i.".code = p".$i.".code
                                AND c".$i.".year = ".$year."
                                AND c".$i.".steps = ".$i." ");
    }
    $totalvalues .=RefTable::find_by_sql("FROM charges c, totalgestion p
    WHERE c.code = p.code
    AND c.annee = ".$year."
    AND c.type = 1") ;

    foreach ($totalvalues as $totalvalue) {
        $Code = $totalvalue->code;
        $Delta = $totalvalue->delta;

        echo $Code . ' - ' .$Delta; 
    }       

This query works fine normally, but i am a newbie in php activerecord, so i hope anybody can show me what i make wrong here.

Thanks in advance

Answer 1

RefTable::find_by_sql is not a query builder. Each time you call it, it is executing a SQL statement. Knowing that, it's easy to see why the first call fails. Try this:

$sql = "SELECT distinct c.code, p.gestion";
for ($i = 1; $i <= count($steps); $i++) {
    $sql .= "
        ,(SELECT IFNULL(ROUND(SUM(c".$i.".`delta`),1),'')
            FROM charge c".$i.", totalgestion p".$i."
            WHERE c".$i.".code = p.code
            AND c".$i.".steps = ".$i."
            AND c".$i.".actif = 1) as delta".$i.",'' as comments".$i.",
         (SELECT IFNULL(ROUND(SUM(c".$i.".`values`),1),'')
            FROM charges c".$i.", totalgestion p".$i."
            WHERE c".$i.".code = p".$i.".code
            AND c".$i.".year = ".$year."
            AND c".$i.".steps = ".$i." "
    ;
}
$sql .= "
FROM charges c, totalgestion p
WHERE c.code = p.code
AND c.annee = ".$year."
AND c.type = 1
";

$totalvalues = RefTable::find_by_sql($sql);