Ordering by a generated field in Doctrine 1.2
I'm using a preDqlSelect() callback to add a "virtual field". But the validation of my query must be happening before the callback get fired because I can't order by that new field when I query that model.
Here's my callback:
class Artist e开发者_如何学运维xtends BaseArtist
{
public function preDqlSelect(Doctrine_Event $event)
{
// Add title field (concatenation of first_name, last_name, and company fields)
$params = $event->getParams();
$q = $event->getQuery();
$a = $params['alias'];
if (
$q->contains($a.'.first_name')
&& $q->contains($a.'.last_name')
&& $q->contains($a.'.company')
) {
$exists = '!ISNULL(NULLIF('.$a.'.%s, \'\'))';
$value = 'IFNULL('.$a.'.%1$s, \'\')';
$if = sprintf($exists, 'first_name').' OR '.sprintf($exists, 'last_name');
$thenPiece1 = sprintf($value, 'first_name').', \' \', '.sprintf($value, 'last_name');
$thenPiece2 = 'IF('.sprintf($exists, 'company').', CONCAT(\' (\', '.sprintf($value, 'company').', \')\'), \'\')';
$then = 'TRIM(CONCAT('.$thenPiece1.', '.$thenPiece2 .'))';
$else = sprintf($value, 'company');
$select = 'IF('.$if.', '.$then.', '.$else.') AS title';
$q->addSelect($select);
}
}
// ...
And here's my query:
$artists = Doctrine_Query::create()
->select('a.id, a.first_name, a.last_name, a.company')
->from('Artist a')
->innerJoin('a.Products p')
->where('a.active <> 0')
->andWhere('p.active <> 0')
->orderBy('a.title')
->execute();
Here's the error I'm getting:
Fatal error: Uncaught exception 'Doctrine_Query_Exception' with message 'Unknown column title' in /[REMOVED]/lib/doctrine/Doctrine/Query/Orderby.php:94 Stack trace: #0 /[REMOVED]/lib/doctrine/Doctrine/Query/Abstract.php(2077): Doctrine_Query_Orderby->parse('a.title') #1 /[REMOVED]/lib/doctrine/Doctrine/Query.php(1160): Doctrine_Query_Abstract->_processDqlQueryPart('orderby', Array) #2 /[REMOVED]/lib/doctrine/Doctrine/Query.php(1126): Doctrine_Query->buildSqlQuery(false) #3 /[REMOVED]/lib/doctrine/Doctrine/Query/Abstract.php(1137): Doctrine_Query->getSqlQuery(Array, false) #4 /[REMOVED]/lib/doctrine/Doctrine/Query/Abstract.php(1106): Doctrine_Query_Abstract->_getDqlCallbackComponents(Array) #5 /[REMOVED]/lib/doctrine/Doctrine/Query/Abstract.php(1001): Doctrine_Query_Abstract->_preQuery(Array) #6 /srv/web/museumfounda in /[REMOVED]/lib/doctrine/Doctrine/Query/Orderby.php on line 94
Something similar to this should work:
->select('a.id, a.first_name, a.last_name, a.company, IF(your constructed query from above) AS title')
This should allow you using you ordering clause as you do it now. To make it nicer, you could create the query inside a Table
class and pass the values from your constructed query from above
so the code is easy to maintain.
精彩评论