Sonata admin createQuery ignores table alias in sort expression

Question

I have two entities: "OriginNews" and "FollowUpNews". They are related one to many, so one OriginNews can have multiple FollowUpNews.

In my OriginNewsAdmin i only want to display the OriginNews in listview, but also ordered (if connected) by FollowUpNews ... so i decided to manipulate my listView-data by altering the createQueryMethod like this.

 /**
 * @param string $context
 * @return ProxyQueryInterface
 */
public function createQuery($context = 'list')
{
    $query = parent::createQuery($context);

    $query->leftJoin('o.followUpNews', 'fun')
        ->addSelect('COALESCE(fun.importDate, o.importDate) AS HIDDEN sortDate')
        ->addOrderBy('sortDate');

    return $query;
}

This should join the followUpNews to the originNews and sort by importdate ... when i run this, ill get the error.

An exception has been thrown during the rendering of a template ("[Semantical Error] line 0, col 94 near 'sortDate ASC,': Error: 'sortDate' is not defined.").

Is sonata admin kidding me? I defined the sortDate field one line above ... even if i remove the "HIDDEN" keyword ... no change! Where is the problem?

My goal is, to have always the OriginNews on top which has the most recent FollowUpNews ... is that possible in another way?

Thanks

Answer 1

Hours of searching for my problem i found out, that this is simple a doctrine bug existing since several years ...

I ran into the same problem as described here

Doctrine Querybuilder ORDER BY clause is not in SELECT list

I also did the workaround with setting the sql mode to null. I installed also doctrine extensions to use IFNULL-function in order by clause.

My sql looks now like following

$query->leftJoin('o.followUpNews', 'fun')
    ->addOrderBy(IFNULL('fun.importDate', 'o.importDate'), 'DESC');

That works for me.