Get Records from database of all months

Question

I am developing a web application in symfony. I want to fetch the records from database according to months. My Query is:

$totalSearchesByAdminAll = $em->createQueryBuilder()
    ->select('count(SearchHistory.id) AS totalSearchesByAdmin')
    ->from('DRPAdminBundle:Log',  'SearchHistory')

    ->where('SearchHistory.last_updated like :last_updated')
    ->setParameter('last_updated',??????.'%')   

    ->andwhere('SearchHistory.event = :event')
    ->setParameter('event','ADMIN_SEARCH')   
    ->getQuery()
    ->getArrayResult();

last_updated is datetime field and stores in database like: 2015-06-12 11:50:44

I want records like Jan=>5 Feb=>10 .....and so on..

Please Help

score 2 · Answer 1 · answered Aug 05 '15 at 14:00

2

You can group your data by month and year with a DATE_FORMAT mysql statement.

In order to use the mysql function in a DQL Doctrine statement i suggest you to install the mapado/mysql-doctrine-functions doctrine function. Simply add to your composer.json and enable it in the config.yml as follow:

#app/config/config.yml

    orm:
        auto_generate_proxy_classes: %kernel.debug%

        entity_managers:
            default:
                mappings:
                  ....
                dql:
                    datetime_functions:
                        date_format: Mapado\MysqlDoctrineFunctions\DQL\MysqlDateFormat

Then you can use in the repository as example:

$qb = $this->createQueryBuilder('p')
    ->select("DISTINCT DATE_FORMAT(p. last_updated,'%m-%Y') as formatted_date")
    ->andwhere('p.SearchHistory.event = :event')
    ->setParameter('event','ADMIN_SEARCH')           
    ->addGroupBy("formatted_date")
    ->orderBy('p.last_updated')
    ->getQuery()
    ->getResult();

You need to modify the example to add the count

Hope this help.

answered Aug 05 '15 at 14:00

Matteo

37,680
11
100
115

I got Error: InvalidTypeException: Invalid type for path "doctrine.orm.entity_managers.default.mappings". Expected array, but got string – Aug 05 '15 at 14:14
hi @KaranSofat is only an example of a config.yml files. Check the doc of the bundle about how to install: https://github.com/mapado/MysqlDoctrineFunctions#use-with-symfony2 – Matteo Aug 05 '15 at 14:16
hi @ Matteo I have install mapado/mysql-doctrine-functions with composer.After that same error occurs. – Aug 06 '15 at 13:30
hi @KaranSofat have you follow the guide correctly as described in the bundle's doc? check the right part of your config.yml files to modify – Matteo Aug 06 '15 at 13:38
when I come to the part "Add the classes to your configuration".I dont know In which file I put that classes. – Aug 06 '15 at 13:42
@KaranSofat, of course, skip that part and check the "Use with Symfony2" where describe how to modify the config.yml and, if you config it succesfully, you don't see the exception – Matteo Aug 06 '15 at 13:45
@ Matteo. Thanks for help.but I could not solve my problem my friend. :( – Aug 06 '15 at 14:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85308/discussion-between-karan-sofat-and-matteo). – Aug 06 '15 at 14:09
hi @KaranSofat i'm in – Matteo Aug 06 '15 at 14:32

score 1 · Answer 2 · answered Aug 05 '15 at 13:51

1

base query:

select count(last_updated), month(last_updated)  from TABLENAME group by month(last_updated)

answered Aug 05 '15 at 13:51

Grumpy

2,140
1
25
38

score 1 · Answer 3 · answered Aug 05 '15 at 14:05

You can group by month in SQL:

 $totalSearchesByAdminAll = $em->createQueryBuilder()
        ->select('count(SearchHistory.id) AS totalSearchesByAdmin')
        ->addSelect('MONTH(last_updated) AS month')
        ->from('DRPAdminBundle:Log',  'SearchHistory')
        ->where('SearchHistory.last_updated >= :last_updated')
        ->setParameter('last_updated','2015-06-12 11:50:44')   
        ->andwhere('SearchHistory.event = :event')
        ->setParameter('event','ADMIN_SEARCH')
        ->groupBy('month')   
        ->getQuery()
        ->getArrayResult();

dont forget to add in your doctrine configuration:

doctrine:
    orm:
        dql:
            string_functions:
                MONTH: DoctrineExtensions\Query\Mysql\Month

there is an example here http://stackoverflow.com/questions/18826836/how-can-i-use-sqls-year-month-and-day-in-doctrine2 — Sylvain Martin, Aug 05 '15 at 14:05

Get Records from database of all months

3 Answers3