Add optional statements to SQL builder like COLLATE

Sometimes you need to use a different COLLATE, to do a select for example, and be case sensitive or accent sensitive.

At the moment this is not possible using the query builder unless you write you own plain sql query.

I think it would be a good idea to add a method like collate() or something generic like additional() (or something like that) that allow to attach non standard statements like COLLATE to the generated query.

I hope I have been clear enough explaining the case.

Can you check how to achieve that for MySQL, SQLite, PostgreSQL, MSSQL and Oracle?

From a simple google search, this is what I have found:

MySQL http://dev.mysql.com/doc/refman/5.5/en/charset-collate.html

MSSQL http://msdn.microsoft.com/en-us/library/ms184391.aspx

SQLite http://www.sqlite.org/datatype3.html#collation

PgSQL http://www.postgresql.org/docs/9.1/static/collation.html

Oracle http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch3globenv.htm#sthref362 :(

Or different sorting options like


SORT BY row DESC NULLS LAST

for example.

I think that would be quite useful.

Can’t you already add these things with CDbCriteria methods? And the sort by nulls last using a CSort?