CODEWITHSUNDEEP
phpsqlsymfonydoctrine-ormdql
keybored
keybored

Reputation: 5248

Symfony and Doctrine Querying For Hour Ranges

In a previous question (SQL query between two times on every day) I asked about querying across a set of data and returning all entries that had dates associated with them that were between two hour values. I'm also trying to exclude Saturday and Sunday. The answer I received is valid when dealing with plain SQL:

SELECT *
FROM yourtable
WHERE TIME(created_at) BETWEEN '08:00:00' AND '15:00:00'

But I actually need to do this while using Symfony2/Doctrine. Not surprisingly when I create the following:

$myQuery= $this->em->createQuery("SELECT f FROM myBundle:Foo f
            WHERE f.bar = 1
            AND TIME(f.myTimestamp) BETWEEN :myStart AND :myEnd
            AND DAYOFWEEK(f.myTimestamp) NOT IN (1,7)") 
                ->setParameter('myStart', $myStart)
                ->setParameter('myEnd', $myEnd)
                ->getResult();

I'm getting exceptions because TIME and DAYOFWEEK are not native functions of Doctrine. Is there anything within Doctrine that I can use to perform a similar query or am I out of luck? Thanks.

Upvotes: 1

Views: 767

Answers (1)

Nicolai Fröhlich
Nicolai Fröhlich

Reputation: 52493

Neither DAYOFWEEK nor TIME, DAY, ... are supported in DQL.

The quickest way to satisfaction will be a native MySQL query.

Upvotes: 1

Related Questions