如何在DQL中按NULL排序？ [英] How can I order by NULL in DQL?

问题描述

我正在使用Symfony2框架构建一个应用程序，并使用Doctrine ORM。我有一张航空公司的表，其中缺少一些IATA代码。我输出一个列表，按照这个IATA代码排序，但是我得到了不合需要的结果，即空值IATA代码的记录在顶部排序。

MySQL这个很简单，就是用$ code> ORDER BY ISNULL（code_iata），code_iata ，但是我对DQL的等价物是无知的。我试过

  $ er-> createQueryBuilder（'airline'） - > orderBy（'ISNULL（airline.codeIata） airline.codeIata'，'ASC'）
  

但这会给我一个语法错误。

教义文档也给我没有答案。有没有办法？

解决方案

DQL不包含纯SQL的每个功能。幸运的是，您可以定义自定义DQL方法来实现此目的。

某些资源

http://punkave.com/window/2012/07/24/for-the-php-crowd-adding-custom-functions-to-doctrine-2-dql

http://docs.doctrine-project.org/en/2.1/cookbook/dql-user-defined-functions.html

< a href =http://symfony.com/doc/2.0/cookbook/doctrine/custom_dql_functions.html =nofollow> http://symfony.com/doc/2.0/cookbook/doctrine/custom_dql_functions.html

I'm building an app using Symfony2 framework and using Doctrine ORM. I have a table with airlines for which some IATA codes are missing. I'm outputting a list, ordered by this IATA code, but I'm getting the undesirable result that the records with null IATA codes are sorted at the top.

In MySQL this is simple enough to do, with ORDER BY ISNULL(code_iata), code_iata but I'm clueless as to what the equivalent would be for DQL. I tried

$er->createQueryBuilder('airline')->orderBy('ISNULL(airline.codeIata), airline.codeIata', 'ASC')

but this gives me a syntax error.

The Doctrine docs give me no answer either. Is there a way?

解决方案

DQL does not contain every function of plain SQL. Fortunately you can define your custom DQL method to accomplish this.

Some resources:

http://punkave.com/window/2012/07/24/for-the-php-crowd-adding-custom-functions-to-doctrine-2-dql

http://docs.doctrine-project.org/en/2.1/cookbook/dql-user-defined-functions.html

http://symfony.com/doc/2.0/cookbook/doctrine/custom_dql_functions.html

这篇关于如何在DQL中按NULL排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！