Yii1 - CActiveDataProvider criteria not working as expected

Yii 1.1 General Discussion
1

I am trying to translate this query to CActiveDataProvider:


    // SELECT p.id "

    //     . "FROM projects AS p "

    //     . "LEFT JOIN purged_files AS pf ON p.id = pf.project_id "

    //     . "WHERE pf.id IS NULL "

    //     . "AND `new_status_id` IN ('DELIVERED', 'PAID') "

    //     . "AND `created` <= '" . date("Y-m-d H:i:s", strtotime("-" . $lifetime_days . " DAYS")) . "' "

    //     . "ORDER BY id DESC "

    //     . "LIMIT 0, 5000")

Current code:


     $criteria->select    = "project.id";

            $criteria->join      = 'LEFT JOIN purged_files ON project.id = purged_files.project_id';

            $criteria->order     = "project.id desc";

            $criteria->condition = "`new_status_id` IN ('DELIVERED', 'PAID') AND `created` <= '" . date("Y-m-d H:i:s", strtotime("-180 DAYS")) . "'";

            $criteria->limit     = 5000;

            $criteria->offset    = 0;

    

            $dataProvider = new CActiveDataProvider('Project', array(

                'criteria'   => $criteria,

            ));

    

            $iterator = new CDataProviderIterator($dataProvider);

    

            foreach ($iterator as $project) {

                echo $project->id . "<br>";

            }

    

            var_dump('<pre>', "end", '</pre>');die;

When I run this I am getting this error:


    CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'project.id' in 'on clause'. The SQL statement executed was: SELECT COUNT(*) FROM `projects` `t` LEFT JOIN purged_files ON project.id = purged_files.project_id WHERE (`t`.`status` <> "DELETED" AND `t`.`status` <> "CANCELED" AND `t`.`status` <> "ARCHIVED") AND (`new_status_id` IN ('DELIVERED', 'PAID') AND `created`, <= '2017-12-29 13:31:42')

Also it seems that limit isn’t working (when I run the criteria with just limit and offset, everything from db is collected). What seems to be the issue here?

2

Problem is that you are using wrong table alias, if you change your ‘select’ line to be like this:


     $criteria->select    = "t.id";

you will not be getting sql errors.

1 Like
3

Cool :). That fixed one error (I don’t know why is trying to use t as alias). But the limit still doesn’t work o.O. Am I using it wrong?

4

You need to set ‘pagination’=>false for offset/limit to work

please check pagionation and limit

1 Like