How to use a findBy method with comparative criteria

Question

I'd need to use a "magic finder" findBy method using comparative criteria (not only exact criteria). In other words, I need to do something like this:

$result = $purchases_repository->findBy(array("prize" => ">200"));

so that I'd get all purchases where the prize is above 200.

Loss

Answer 1

This is an example using the Expr() Class - I needed this too some days ago and it took me some time to find out what is the exact syntax and way of usage:

/**
 * fetches Products that are more expansive than the given price
 * 
 * @param int $price
 * @return array
 */
public function findProductsExpensiveThan($price)
{
  $em = $this->getEntityManager();
  $qb = $em->createQueryBuilder();

  $q  = $qb->select(array('p'))
           ->from('YourProductBundle:Product', 'p')
           ->where(
             $qb->expr()->gt('p.price', $price)
           )
           ->orderBy('p.price', 'DESC')
           ->getQuery();

  return $q->getResult();
}

Brahear

Avoid using DQL if not strictly needed, it's just making your logic more and more coupled to the ORM. – Grapeshot

@Sliq this is a doctrine behaviour and doesn't necessarily have anything to do with symfony. – Brahear

@Sliq after you try some more frameworks, you'll realize that Symfony isn't so crappy – Brisk

If I see correctly, this function is a repository method. Here, you can go straight to $this->createQueryBuilder('p') instead of going around, via EntityManager: $this->getEntityManager()->createQueryBuilder(). – Dorsey

@Grapeshot Can you expand on this please? (not using DQL -- isn't that the core of doctrine?) – Aeronaut

Answer 2

The class Doctrine\ORM\EntityRepository implements Doctrine\Common\Collections\Selectable API.

The Selectable interface is very flexible and quite new, but it will allow you to handle comparisons and more complex criteria easily on both repositories and single collections of items, regardless if in ORM or ODM or completely separate problems.

This would be a comparison criteria as you just requested as in Doctrine ORM 2.3.2:

$criteria = new \Doctrine\Common\Collections\Criteria();
$criteria->where(\Doctrine\Common\Collections\Criteria::expr()->gt('prize', 200));

$result = $entityRepository->matching($criteria);

The major advantage in this API is that you are implementing some sort of strategy pattern here, and it works with repositories, collections, lazy collections and everywhere the Selectable API is implemented.

This allows you to get rid of dozens of special methods you wrote for your repositories (like findOneBySomethingWithParticularRule), and instead focus on writing your own criteria classes, each representing one of these particular filters.

Grapeshot

Note: I'm using symfony 2.8.11 with doctrine and - maybe just there - it's "Criteria::expr()->gt()", not "$criteria->expr()->gt()". – Autotoxin

It's a static method: github.com/doctrine/collections/blob/… Also: Symfony is NOT Doctrine. Reference doctrine stuff with doctrine names and versioning :-P – Grapeshot

@Grapeshot then it should be $criteria::expr()->gt() ideally, no? – Thrum

Criteria::expr() also OK - feel free to edit the answer. – Grapeshot

I do get an error here. I try to loop over $result in a twig template and only get Unrecognized field: 0 pointing to the line {% for r in result %}. – Joggle

Having concrete repository methods like findOneBySomethingWithParticularRule is a good thing IMO as it decouples your business logic from Doctrine implementation details like the criteria builder. – Bloxberg

Answer 3

You have to use either DQL or the QueryBuilder. E.g. in your Purchase-EntityRepository you could do something like this:

$q = $this->createQueryBuilder('p')
          ->where('p.prize > :purchasePrize')
          ->setParameter('purchasePrize', 200)
          ->getQuery();

$q->getResult();

For even more complex scenarios take a look at the Expr() class.

Ralleigh

Avoid using DQL where not strictly necessary. It locks you into the ORM specific API, and is not really re-usable. There are some cases where DQL is required, but this is not one of those. – Grapeshot

how does using the QueryBuilder not lock you into doctrine in exactly the same way? – Berri

Answer 4

$criteria = new \Doctrine\Common\Collections\Criteria();
    $criteria->where($criteria->expr()->gt('id', 'id'))
        ->setMaxResults(1)
        ->orderBy(array("id" => $criteria::DESC));

$results = $articlesRepo->matching($criteria);

Mosaic

This is not working for me, see #49451470 – Joggle

Answer 5

The Symfony documentation now explicitly shows how to do this:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
    'SELECT p
    FROM AppBundle:Product p
    WHERE p.price > :price
    ORDER BY p.price ASC'
)->setParameter('price', '19.99');    
$products = $query->getResult();

From http://symfony.com/doc/2.8/book/doctrine.html#querying-for-objects-with-dql

Oscitancy

Answer 6

I like to use such static methods:

$result = $purchases_repository->matching(
    Criteria::create()->where(
        Criteria::expr()->gt('prize', 200)
    )
);

Of course, you can push logic when it is 1 condition, but when you have more conditions it is better to divide it into fragments, configure and pass it to the method:

$expr = Criteria::expr();

$criteria = Criteria::create();
$criteria->where($expr->gt('prize', 200));
$criteria->orderBy(['prize' => Criteria::DESC]);

$result = $purchases_repository->matching($criteria);

Dorsey

Answer 7

Copying the findBy query and modifying it to return your expected result is a good approach.

Cherida