doctrine2 random

For some really good reasons or, doctrine2 do not implement the RAND() fonction to sort randomly your query results. Here some dirty ways to do it. Don’t use them it’s bad.

One solution is to shuffle your collection of rows using php

      public function getRandom($max = 10,$site) {
        $results = $this->createQueryBuilder('u')
                ->where('u.site = :site')
                ->setParameter('site', $site)
                ->orderBy('u.sort', 'DESC')
                ->setMaxResults($max)
                ->getQuery()
                ->getResult();
        shuffle($results);
        return $results;
    }

In this solution, you retrieve the last 10 rows and shuffle them after. Peformances are not too bad but you will always retrieves the same last rows from your table.

Another solution is to use the array_rand php fonction

      public function getRandom($site) {
        $results = $this->createQueryBuilder('u')
                ->where('u.site = :site')
                ->setParameter('site', $site)
                ->orderBy('u.sort', 'DESC')
                ->getQuery()
                ->getResult();
        $result2 = array_rand($results);
        return $result2;
    }

In this case you fetch all rows from your table, this could be slow and memory consuming…

If you need to retrieve only one row, you can use somethinfg like this

 public function getOneRandom()
{
$em = $this->getEntityManager();
$max = $em->createQuery('
SELECT MAX(q.id) FROM questions q
')
->getSingleScalarResult();
return $em->createQuery('
SELECT q FROM questions q
WHERE q.id >= :random
ORDER BY q.id ASC
')
->setParameter('random',rand(0,$max))
->setMaxResults(1)
->getSingleResult();
}

This solution can only be used if you want to retrieve any of the tables rows, if you add a filtrer you may return an empty result.

This solution is not dirty it just use 2 queries instead of one. the tips is to use the offset. And you can use a filter !!!

$qCount = Doctrine::getTable('Questions')
     ->createQuery()
     ->select('count(*)')
     ->where('site = :site')
     ->setParameter('site', $site)
     ->fetchOne(array(), Doctrine::HYDRATE_NONE);
$question = Doctrine::getTable('Questions')
     ->createQuery()
     ->select('*')
     ->where('site = :site')
     ->setParameter('site', $site)
     ->limit(1)
     ->offset(rand(0, $qCount[0] - 1))
     ->fetchOne();

And you still can use native queries : http://docs.doctrine-project.org/en/latest/reference/native-sql.html

Doctrine2 override save method

With Doctrine2 you cannot override save method but you can execute preUpdate() method.
See below how to implement it.

On your entity class, for example AdminBundle/Entity/Annonce.php, add the HasLifecycleCallbacks annontation

/**
 * @ORM\Entity(repositoryClass="Tripix\AdminBundle\Repository\AnnonceRepository")
 * @ORM\HasLifecycleCallbacks
 */

And your method, with the annotation

/** @ORM\PreUpdate() */
        public function preUpdate()
        {
                $this->created_at = new \DateTime("now");
        }

That’s all, it should works.

A complete example, just in case :

<?php
namespace Tripix\AdminBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
use Doctrine\Common\Collections\ArrayCollection;


/**
 * @ORM\Entity(repositoryClass="Tripix\AdminBundle\Repository\AnnonceRepository")
 * @ORM\HasLifecycleCallbacks
 */
class Annonce
{

/** @ORM\PreUpdate() */
        public function preUpdate()
        {
                $this->created_at = new \DateTime("now");
        }

    public function __construct()
    {
        $this->created_at = new \DateTime("now");
        $this->visuels = new ArrayCollection();
    }