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.


on 06.13.10 reported by: housebolt owned by: nate

Updates

on 07.05.10 by alkemann
This is where this happens:

http://rad-dev.org/lithium/source/branches/x-data/libraries/lithium/data/source/Database.php#612
on 08.26.10 by ddebernardy
Possible fix:

<http://rad-dev.org/forks/ddebernardy/lithium/commits/view/1856e653ddeec2466fbecca4c83f4306c9b1de43>
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
This is working now.