Buy

Creating a Custom orderBy Query

Ok friends, the homepage lists every event in the order they were added to the database. We can do better! Head to EventController and replace the findAll method with a custom query that orders the events by the time property, so we can see the events that are coming up next first:

// src/Yoda/EventBundle/Controller/EventController.php
// ...

public function indexAction()
{
    $em = $this->getDoctrine()->getManager();

    $entities = $em
        ->getRepository('EventBundle:Event')
        ->createQueryBuilder('e')
        ->addOrderBy('e.time', 'ASC')
        ->getQuery()
        ->execute()
    ;

    // ...
}

When we check the homepage, it looks about the same as before. Let’s complicate things by only showing upcoming events:

$entities = $em
    ->getRepository('EventBundle:Event')
    ->createQueryBuilder('e')
    ->addOrderBy('e.time', 'ASC')
    ->andWhere('e.time > :now')
    ->setParameter('now', new \DateTime())
    ->getQuery()
    ->execute()
;

This uses the parameter syntax we saw before and uses a \DateTime object to only show events after right now.

To test this, edit one of the events and set its time to a date in the past. When we head back to the homepage, we see that the event is now missing from the list!

Moving Queries to the Repository

This is great, but what if we want to reuse this query somewhere else? Instead of keeping the query in the controller, create a new method called getUpcomingEvents inside EventRepository and move it there:

// src/Yoda/EventBundle/Entity/EventRepository.php
// ...

/**
 * @return Event[]
 */
public function getUpcomingEvents()
{
    return $this
        ->createQueryBuilder('e')
        ->addOrderBy('e.time', 'ASC')
        ->andWhere('e.time > :now')
        ->setParameter('now', new \DateTime())
        ->getQuery()
        ->execute()
    ;
}

Now that we’re actually inside the repository, we just start by calling createQueryBuilder(). In the controller, continue to get the repository, but now just call getUpcomingEvents to use the method:

// src/Yoda/EventBundle/Controller/EventController.php
// ...

public function indexAction()
{
    $em = $this->getDoctrine()->getManager();

    $entities = $em
        ->getRepository('EventBundle:Event')
        ->getUpcomingEvents()
    ;

    // ...
}

Note

The $em->getRepository('EventBundle:Event') returns our EventRepository object.

Whenever you need a custom query: create a new method in the right repository class and build it there. Don’t create queries in your controller, seriously! We want your fellow programmers to be impressed when you show them your well-organized Jedi ways.

Leave a comment!

  • 2015-05-17 weaverryan

    Hi there!

    I'll admit, I don't know anything about Windows and SQL server stuff :). But I will say that the stuff in parameters.yml (which is just being passed to the doctrine.dbal key in app/config/config.yml) is passed to the constructor of Doctrine Connection object: http://doctrine-dbal.readthedo.... So, if you're a bit brave, you might be able to debug it there. Also, in config.yml, here is the valid config you can put, in addition to dbname, password, etc: http://symfony.com/doc/current... (look under doctrine.dbal.connections.default - all the keys you see there are valid for you to use).

    I hope that helps, at least a little :).

    Cheers!

  • 2015-05-16 Enkhbilguun E.

    Hi everyone,

    I can run a stored procedure or just plain query from my Symfony 2.6. I've written a stored procedure that calls another stored procedure that interacts with a historian. It looks the historian wants me provide a domain user. I can't make it work.

    Do you know how I can use Windows domain user (my web server is IIS 7.5) as my database (pdo_sqlsrv) access credential?

    I tried empty username/password and domain\user_account & password in the parameters without changing anything. It looks it's working.

  • 2015-05-16 Enkhbilguun E.

    getManager() work. Thanks Ryan. :-)

  • 2015-05-14 weaverryan

    Hey!

    Thanks for posting the solution to your question here for others :).

    About getEntityManager() being deprecated, just use getManger() instead - it returns the same exact thing (so nothing else needs to change) - we just renamed the method in 2.1.

    Cheers!

  • 2015-05-10 Enkhbilguun E.

    Ryan,

    I'm sorry. I've found the answer. I should have just execute the stored procedure in the same way as I run in the SQL management studio.

    Error I was getting was because of the permission issue. The passing credentials running Symfony to the Linked Server was not right user.

    I'm using getEntityManager(), and Symfony says is deprecated since v2.1. Can you tell me the current method I should use for plain SQL query?

    ===
    $conn = $container->get('doctrine')->getEntityManager()->getConnection();
    $sql = 'SELECT * FROM yoda_event';
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    var_dump($stmt->fetchAll());die;
    ===

  • 2015-05-09 Enkhbilguun E.

    Hi Ryan,

    How can I call a stored procedure in MSSQL database? Connection between my Symfony v2.6 and SQL Server 2008R2 has been established, and I can do some basics.