Problems with Postgresql and Capital letters in tables and rows

Archives Yii 1.0.x Discussions
1

I tried the post example that comes with yii in Postgresql database, but I had a lot of problems with the capital letters in the tables and rows.

To use capitals in postgresql the sql query needs to use blockquotes in table & col names, and sometimes yii’s ActiveRecord does it but sometimes doesn’t.

Is it a known bug or a misconfiguration in my database settings or yii settings?

I modified the post example code to use only lowercase and the database to be lowercase in tables and cols and it worked fine. I’m posting it also because almost all examples in yii are with tables/cols with capitals, so maybe a lot of people are using this way of database standard (now i’m using undercores so separate cols words because of this issue).

Just to help.

Thank you all!

Christian

2

After turning logging on, could you please submit some nasty queries which failed on database-side?

3

Yes, there are some problems:

http://www.yiiframework.com/forum/index.php?/topic/3088-yii-postgres-and-tables-inheritance/page__p__17569__hl__postgresql__fromsearch__1&#entry17569

But generally it’s OK. I’m currently use it with Yii.

4

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

5

Use Yii::app()->db->quoteColumnName() where appropriate.

6

You need to write it like this:




          $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);
7

Yes I know I can alter the code, but shouldn’t it be easier if yii did it automatically?

In mysql and other databases the code works, it should be the same with postgres.

I mean, is this an error which may be reported or a misconfiguration.

8

You wrote it the same way i did.

This is the right way:


                $criteria=new CDbCriteria;

                $criteria->condition='status='.Post::STATUS_PUBLISHED;

                $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);

                $criteria->order='createTime DESC'; //order by after counting
9

christian

I have quoted createTime.

10

Yii can’t automatically recognize column names. This should not be expected as you manually typed in your query details.

11

Quoting createTime doesn’t work, this is the error

2009/08/13 13:18:34 [error] [system.db.CDbCommand] Error in querying SQL: SELECT COUNT(*) FROM "Post" WHERE status=1 ORDER BY "createTime" DESC

2009/08/13 13:18:34 [error] [exception.CDbException] exception ‘CDbException’ with message ‘CDbCommand failed to execute the SQL statement: SQLSTATE[42803]: Grouping error: 7 ERROR: column “Post.createTime” must appear in the GROUP BY clause or be used in an aggregate function’ in /home/christian/public_html/yii/framework/db/CDbCommand.php:322

The only way is moving the $criteria->order after the postCount as I did.

12

That’s not Yii part. It’s your query this time. Why are you sorting count(*)?

13

I’m not sorting count. Yii blog does it the way it comes.

I know it’s absurd. Postgresql throws an error (maybe mysql also has count with sort but pass it by).

Maybe ActiveRecord should filter sort to not be included when doing count functions. At least in postgres

14

Actually you can use both sort and count when used with group by. I think it is why order is not discarded with count queries.

I’ve reported some PostgreSQL problems here http://code.google.com/p/yii/issues/detail?id=437 but I guess I did it too early without discussing them first. I believe this one marked as invalid because of that.

15

Ok thanks samdark. I’ll be following the issue.

Best!

16

This issue is marked as invalid. So there will be no progress… at least in that issue ticket.

17

hello

its very painful to use quoteColumnName() everytime.

isnt it possible to define it on db section of config file?

18

ps47r,

Why don’t you create a global shortcut function like column() for this scenario?