Trying to understand Doctrine Criteria() and if caching is possible

Question

I need to write a simple query: SELECT propertyName from TABLE_NAME WHERE abc > 10

I believe I can't do this with a simple $repository->findBy() so believe that DQL (or the query builder) would do the job. Something like this:

$query = $this->createQueryBuilder('x')
      ->where('x.abc > :amt')
      ->setParameter('amt', 10)
      ->getQuery();

$query->getResult();

However, I hear that Doctrine "Criteria", which was available from Doctrine 2.3, is a better method.

(1) I see very little documentation and still DQL is promoted the most. Could someone give me a very simple code sample that is the same as my one above (but using Criteria)?

(2) What about caching? I know I can do this with DQL, but what about Criteria?

$query->useResultCache('cache_id');

score 6 · Accepted Answer · edited May 23 '17 at 12:25

From the docs:

The Criteria has a limited matching language that works both on the SQL and on the PHP collection level. This means you can use collection matching interchangeably, independent of in-memory or sql-backed collections.

Another interesting answer can be: How to use a findBy method with comparative criteria

BTW I presume the caching is depends only in the Query object itself and the Doctrine layer, so Criteria is only for write custom selection or matching mechanism.

So you can write your query in a most elegant form as:

    $qb = $this->createQueryBuilder('x'); 
    $query = $qb
        ->where($qb->expr()->gt("x.abc",":amt"))
        ->setParameter('amt', 10)
        ->getQuery();

hope this help

I use it for querying directly in collection relation.

Only for example, this is how i use Doctrine Criteria:

class User implements AdvancedUserInterface
{

  ....

    /**
     * One-To-Many
     *
     * @ORM\OneToMany(targetEntity="Acme\ADemoBundle\Entity\Transaction", mappedBy="user")
     * @ORM\OrderBy({"position" = "ASC"})
     */
    protected $transactions;

    public function getLastTransaction()
    {
        $criteria = Criteria::create()
                        ->orderBy(array('eventAt'=>'DESC'))
                        ->setFirstResult(0)
                        ->setMaxResults(1);

        return $this->transactions->matching($criteria)->first();
    }
 }

Is there any way you know how we could use the result cache for this? — Michal, Feb 21 '17 at 07:47
The link "from the docs" is dead, use this instead: https://www.doctrine-project.org/projects/doctrine-orm/en/2.8/reference/working-with-associations.html#filtering-collections — Alejandro Fiore, Feb 09 '21 at 14:18

Trying to understand Doctrine Criteria() and if caching is possible

1 Answers1