Here’s how i set it up:
Postgresql SQL (adapted from Mysql). The "" are set in each table and column to preserve capital letters. The database structure is attached.
I did nothing but set up config/main to use this pg db.
First problem:
2009/08/13 00:21:26 [error] [system.db.CDbCommand] Error in querying SQL: SELECT COUNT(*) FROM "Post" WHERE status=1 ORDER BY createTime DESC
2009/08/13 00:21:26 [error] [exception.CDbException] exception ‘CDbException’ with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[42703]: Undefined column: 7 ERROR: column “createtime” does not exist
LINE 1: …LECT COUNT(*) FROM "Post" WHERE status=1 ORDER BY createTime…
^' in /home/christian/public_html/yii/framework/db/CDbCommand.php:322
This works if createTime is with blockquotes in the sql query, this way (because of the capital letters, postgresql doesn’t recognize the column)
SELECT COUNT(*) FROM "Post" WHERE "status"=1 ORDER BY "createTime" DESC
There are some other errors in this blog example related to the same issue.
One that makes me worry a bit, in the PostController to show the list of posts ordered there’s the next code
$criteria=new CDbCriteria;
$criteria->condition='status='.Post::STATUS_PUBLISHED;
$criteria->order='createTime DESC';
$withOption=array('author');
if(!empty($_GET['tag']))
{
$withOption['tagFilter']['params'][':tag']=$_GET['tag'];
$postCount=Post::model()->with($withOption)->count($criteria);
}
else
$postCount=Post::model()->count($criteria);
That generates the next error:
2009/08/11 22:12:37 [error] [system.db.CDbCommand] Error in querying SQL: SELECT COUNT(*) FROM "post" WHERE status=1 ORDER BY createTime DESC
2009/08/11 22:12:37 [error] [exception.CDbException] exception ‘CDbException’ with message ‘CDbCommand failed to execute the SQL statement: SQLSTATE[42803]: Grouping error: 7 ERROR: la columna «post.createtime» debe aparecer en la cláusula GROUP BY o ser usada en una función de agregación’ in /var/sites/yii/framework/db/CDbCommand.php:322
Stack trace:
This is because yii does a count for the pagination, but the order by is included in the query, so pg cannot execute it.
This can be solved removing the order by, with a query like
SELECT COUNT(*) FROM "post" WHERE status=1
Hope this helps!
Is there something i can help?
Best!
Christian