How to ORDER BY DateTime in Doctrine 2?

Question

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

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 !

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

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

 ["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

Immediate

precision=0, scale=0, are they required when field type is datetime ? strange.. remove these two as they are for number types, and then try.. correct me if I am wrong. – Thatcher

you are right, its only for decimal :) – Immediate

I still can't have it working :( any help on this – Immediate

$qb->add('orderBy', 'p.created DESC'); – Eulalia

this is the same as what I'm doing, doesn't work :/ Correct me if I'm wrong but ->add('orderBy', 'e.dateStart') is the same as ->orderBy('e.dateStart', 'ASC') ? – Immediate

Is that the exact query? If so: you don't need the andX() there because you only have 1 expression. If not, show us the exact query. We need to know if there are things like JOIN clauses, or if you're using the Doctrine Paginator, etc... – Applique

This query works without the orderBy. When I add the OrderBy, it just fails. Doctrine 2 doesn't seem be able to sort by a datetime field – Immediate

Doctrine is perfectly capable of ordering results by a datetime property. If the order comes out wrong, you are doing something you're not telling us... – Applique

@MilesM.: If the order just fails with the orderBy, then what's the error message? Do you see anything in the logs?The only thing that strikes me (apart from the spare andX, which Jasper mentioned), is that the DB column is named differently than the entity property. – Owing

"it just fails" is not helpful. What is the error message? – Pouliot

thanks for your help guys. I've updated my question yesterday, it isn't failing anymore, it simply doesn't order anything at all. I am very confused with that .. – Immediate

Please provide a sample of the un-ordered result and which displays the e.dateStart; and could you identify the actual dbms data type of that field (not what you see in your ORM) – Ackley

Answer 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).

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.

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).

Applique

Thank You Jasper. here are my annotations for this field. IT IS DATETIME. /** * @var \DateTime * * @ORM\Column(name="date_start", type="datetime", nullable=true, unique=false) */ private $dateStart; – Immediate

@Miles M. I'm not talking about the mapping metadata (annotations), but about the actual database. (It's not Doctrine performs the query, thus ordering the result, it just generates the query. It's the database that executes the query.) – Applique

Answer 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.

Meprobamate

ho didn't know there was a specific order, where can I learn what's the correct order ? I've been going through the D2 doc many many times but never seen that – Immediate

This is actually not working after several tests. I have updated my answer to paste my actual query. – Immediate

docs.doctrine-project.org/en/2.0.x/reference/query-builder.html - This article is useful for you. See point 13.2.5. The Expr – Thatcher

which parameters are you talking about @Meprobamate ? The order of method calls doesn't count here. – Pouliot

Answer 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:

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

Dimension

the getter has nothing to do with DQL. – Pouliot

Which getters? this is what happen when I echo SQL: SELECT e0_.id AS id0, e0_.name AS name1, e0_.description AS description2, e0_.link AS link3, e0_.social_media_link AS social_media_link4, e0_.booking_link AS booking_link5, e0_.price AS price6, e0_.currency AS currency7, e0_.picture AS picture8, e0_.date_created AS date_created9, e0_.date_start AS date_start10, e0_.date_end AS date_end11, e0_.popularity AS popularity12, e0_.confirmed AS confirmed13, e0_.user_id AS user_id14, e0_.admin_id AS admin_id15, e0_.place_id AS place_id16 FROM event e0_ ORDER BY e0_.date_start ASC LIMIT 200 OFFSET 0 – Immediate

with the exact query you suggested (simplier) – Immediate

Answer 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();

Outdate

Fails for me: I am getting [Syntax Error] line 0, col 191: Error: Expected known function, got 'date' (Doctrine 2.8.x). – Spongin

Answer 5

The order of the query parameter matters. In your case, the query should be:

    $events = $qb->select( 'e' )
        ->from( 'Entity\Event',  'e' )
        ->orderBy('e.dateStart', 'DESC')
        ->setFirstResult( $offset )
        ->setMaxResults( $limit )
        ->getQuery()
        ->getResult();

Not entirely sure what you mean by '20 first events happening from the soonest to the latest', but that should you give you the latest set of events. To change the order change 'DESC' to 'ASC'.

Oswell