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
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.
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With