Ticket Details
Database model query option 'order' throws exception when in format array('field' => 'direction') on MySQL
BUG Ticket (closed)
###What happened:
With a fresh install and a Post table/model with a 'created' field on MySQL 5.1.44
```Post::find('all', array('order' => array('created' => 'ASC')));```
**Creates the following query:**
```"SELECT * From posts ORDER BY Post.created ASC LIMIT 50"```
**And throws the following exception:**<br />
Fatal error: Uncaught exception 'Exception' with message 'SELECT * From posts ORDER BY Post.created ASC LIMIT 50;: **Unknown column 'Post.created' in 'order clause''** in /Users/garret/Sites/Projects/lithium-0.9.5/libraries/lithium/data/source/database/adapter/MySql.php:324
**Stack trace:** <br />
0 /Users/garret/Sites/Projects/lithium-0.9.5/libraries/lithium/core/Object.php(221): lithium\data\source\database\adapter\{closure}(Object(lithium\data\source\database\adapter\MySql), Array, NULL)<br />
1 /Users/garret/Sites/Projects/lithium-0.9.5/libraries/lithium/data/source/database/adapter/MySql.php(327): lithium\core\Object->_filter('lithium\data\so...', Array, Object(Closure))<br />
2 /Users/garret/Sites/Projects/lithium-0.9.5/libraries/lithium/core/Object.php(166): lithium\data\source\database\adapter\MySql->_execute('SELECT * From p...')<br />
3 /Users/garret/Sites/Projects/lithium-0.9.5/libraries/lithium/data/source/Database.php(233): lithium\core\Object->invokeMethod('_execute', Array)<br />
4 /Users/garret/Sites/Projects/lithium-0.9.5/librari in /Users/garret/Sites/Projects/lithium-0.9.5/libraries/lithium/data/source/database/adapter/MySql.php on line 324
As a string it works fine, but when split via array it throws the exception.
**The following code does work (uses a string instead of an array):**<br />
```Post::find('all', array('order' => 'created ASC'));```
###What was expected:
The query should be:<br />
```"SELECT * FROM posts AS Post ORDER BY Post.created ASC LIMIT 50"```<br />
**OR**<br />
```"SELECT * FROM posts ORDER BY posts.created ASC LIMIT 50"```<br />
**OR**<br />
```"SELECT * FROM posts ORDER BY created ASC LIMIT 50"```
I was under the impression that the 'order' option only took strings after I ran into this problem, but looking at the code, it does allow for arrays in \lithium\data\source\Database::order() (line 521).
The actual query is missing the "AS Post" modifier, or the 'Post.' needs to be removed from the ORDER BY string. I'm not sure about other databases, but this happens on my MySQL server when I try to run the query.
Updates
on 07.05.10
by alkemann
on 08.26.10
by ddebernardy
on 10.08.10
by nate
- owner was changed to nate
(fixed)
on 02.28.11
by nate
- status was changed to closed
- resolution was changed to fixed