如何在Doctrine 2中按日期时间排序？

I'm looking to execute the following query:

我想执行以下查询：

        $qb = $this->getEntityManager()->createQueryBuilder();

        $qb->select( 'e' )
            ->from( 'Entity\Event',  'e' )
            ->setMaxResults( $limit )
            ->setFirstResult( $offset )
            ->orderBy('e.dateStart', 'ASC');

        $events = $qb->getQuery()->getResult();

Where

哪里

/**
 * User
 *
 * @ORM\Table(name="event")
 * @ORM\Entity(repositoryClass="Repositories\EventRepository")
 */
class Event
{
    /**
     * @var \DateTime
     *
     * @ORM\Column(name="date_start", type="datetime", precision=0, scale=0, nullable=true, unique=false)
     */
    private $dateStart;

...
}

But the order by doesn't work. My results are not displayed by date start.

但订单不起作用。我的结果不会在日期开始时显示。

I'm looking to retrieve the 20 first events happening from the soonest to the latest

我正在寻找从最快到最晚发生的20个首发事件

How can I do this ?

我怎样才能做到这一点 ？

Thanks

谢谢

EDIT:

编辑：

Following the previous answer, I'm updating my query. unfortunatey I still can't have it working. Please help

按照上一个答案，我正在更新我的查询。不幸的是，我仍然无法工作。请帮忙

     $qb->select( 'e' )
        ->from( 'Entity\Event',  'e' )
        ->Where( 
            $qb->expr()->andX(
                $qb->expr()->between('e.dateStart', ':from', ':to')
            )
        )
        ->orderBy('e.dateStart', 'ASC')
        ->setFirstResult( $offset )
        ->setMaxResults( $limit );

Thanks

谢谢

EDIT 2: It seems moving orderBy did make a difference. I don't have any error as of right now. The script is running fine with orderBy BUT it is NOT ORDERED by datetime at all !

编辑2：似乎移动秩序确实有所作为。我现在没有任何错误。使用orderBy脚本运行正常但是它根本没有按日期时间排序！

In my results I can't see anything that would make me think it has been ordered based on any given property, definitively not datetime !

在我的结果中，我看不到任何会让我觉得它已根据任何给定的属性订购，最终不是日期时间！

How is that possible ?

怎么可能？

The Datetime field looks like something like that in the DB: 2014-05-24 19:30:00

Datetime字段看起来像DB中的那样：2014-05-24 19:30:00

When I var Dump the queries that comes out of the previous query, here is what I have for the datetie field:

当我转储从前一个查询出来的查询时，这里是我对datetie字段的看法：

 ["dateStart"]=> string(8) "DateTime"

Does that mean it's really a string for doctrine and that's why it is not sorted by datetime ?

这是否意味着它真的是一个学说的字符串，这就是为什么它没有按日期时间排序？

Thanks

谢谢

4 个解决方案

#1

Database

You should check the table structure in the database:

您应该检查数据库中的表结构：

What type of column is date_start?
It should be DATETIME (in MySQL, other vendors may vary).
But I suspect it is some form of string, like VARCHAR (or CHAR, TEXT, etc).

什么类型的列是date_start？它应该是DATETIME（在MySQL中，其他供应商可能会有所不同）。但我怀疑它是某种形式的字符串，如VARCHAR（或CHAR，TEXT等）。

When this is the case, the result is ordered, but not in the way you expect it. This is because different types are ordered differently by the database.

在这种情况下，结果是有序的，但不是按照您期望的方式。这是因为数据库对不同类型的排序方式不同。

CLI tool

Doctrine comes with a console tool that's able to validate your mappings and check if the database is consistent with them: doctrine orm:validate-schema.

Doctrine附带一个控制台工具，可以验证您的映射并检查数据库是否与它们一致：doctrine orm：validate-schema。

If it reports inconsistencies with the database, use doctrine orm:schema-tool:update --dump-sql to have it display the queries it would perform to update the database (it will assume the mappings are the source of truth).

如果它报告与数据库的不一致，请使用doctrine orm：schema-tool：update --dump-sql让它显示它将更新数据库所执行的查询（它将假设映射是事实的来源）。

#2

your Query would be like :

您的查询将如下：

    $qb->select( 'e' )
        ->from( 'Entity\Event',  'e' )
        ->orderBy('e.dateStart', 'ASC');
        ->setFirstResult( $offset )
        ->setMaxResults(20);

you have to respect the order of query builder parameters i hope that will help.

你必须尊重查询构建器参数的顺序，我希望这会有所帮助。

#3

Could you give me the result of:

你能告诉我结果：

$qb->select( 'e' )
    ->from( 'Entity\Event',  'e' )
    ->setMaxResults( $limit )
    ->setFirstResult( $offset )
    ->orderBy('e.dateStart', 'ASC');

die($qb->getQuery()->getSql());

One possible thougth: Is your getter for the property $dateStart is well declared ? It should be:

一个可能的问题：你的财产$ dateStart的获取是否得到了很好的宣传？它应该是：

public function getDateStart()
{
    return $this->dateStart;
}

#4

Maybe you can rewrite your code like this

也许你可以像这样重写你的代码

 $qb = $this->getEntityManager()->createQueryBuilder();

    $qb->select( 'e' )
        ->from( 'Entity\Event',  'e' )
        ->setMaxResults( $limit )
        ->setFirstResult( $offset )
        ->orderBy('date(e.dateStart) ASC');//this is the trick that works for me

    $events = $qb->getQuery()->getResult();