Doctrine findBy 'does not equal'

Question

How do I do

WHERE id != 1

In Doctrine?

I have this so far

$this->getDoctrine()->getRepository('MyBundle:Image')->findById(1);

But how do I do a "do not equals"?

This maybe daft, but I cannot find any reference to this?

Thanks

score 73 · Answer 1 · edited May 30 '18 at 13:29

73

There is now a an approach to do this, using Doctrine's Criteria.

A full example can be seen in How to use a findBy method with comparative criteria, but a brief answer follows.

use \Doctrine\Common\Collections\Criteria;

// Add a not equals parameter to your criteria
$criteria = new Criteria();
$criteria->where(Criteria::expr()->neq('prize', 200));

// Find all from the repository matching your criteria
$result = $entityRepository->matching($criteria);

edited May 30 '18 at 13:29

Martijn

15,791
4
36
68

answered Aug 21 '14 at 07:58

El Yobo

14,823
5
60
78

4

At the time that the accepted answer was accepted it was the correct answer :) A couple of years later the situation changed! – El Yobo Mar 28 '17 at 02:36
3

I suggest to update with: use Doctrine\Common\Collections\Criteria; $expr = Criteria::expr(); $criteria = Criteria::create(); – Jpsy Sep 11 '17 at 15:16
@Jpsy I haven't kept up with Doctrine, is there some reason to prefer that? – El Yobo Sep 13 '17 at 03:48
1

@ElYobo: Doctrine\Common\Collections\Criteria is defined as a static class with static methods (see: http://www.doctrine-project.org/api/collections/1.3/class-Doctrine.Common.Collections.Criteria.html). So it should be accessed statically using `CLASS::method()` instead of instantiating it using `new CLASS()`. – Jpsy Sep 13 '17 at 07:49
Yeah, you're right. The snippet I copied was from the link I gave above and the example code is from Ocramius, one of the Doctrine developers, so perhaps that API has changed since it was written. – El Yobo Sep 14 '17 at 00:47
I've added it, as it now is a standalone answer and without it, it doesnt work, which might confuse a novice :) – Martijn May 30 '18 at 13:30

score 54 · Accepted Answer · edited Sep 18 '14 at 14:04

54

There is no built-in method that allows what you intend to do.

You have to add a method to your repository, like this:

public function getWhatYouWant()
{
    $qb = $this->createQueryBuilder('u');
    $qb->where('u.id != :identifier')
       ->setParameter('identifier', 1);

    return $qb->getQuery()
          ->getResult();
}

Hope this helps.

edited Sep 18 '14 at 14:04

The Unknown Dev

3,039
4
27
39

answered Dec 30 '12 at 08:45

BADAOUI Mohamed

2,146
1
17
14

I am not sure how this answers the question as it seeks to match a record based on the value -1, rather than query for values that do not equal one – frak Aug 17 '13 at 20:14
This answer was correct at the time that it was written, but it's now supported; see my answer elsewhere on this question. – El Yobo Sep 30 '14 at 22:47

score 24 · Answer 3 · answered May 24 '13 at 21:16

24

To give a little more flexibility I would add the next function to my repository:

public function findByNot($field, $value)
{
    $qb = $this->createQueryBuilder('a');
    $qb->where($qb->expr()->not($qb->expr()->eq('a.'.$field, '?1')));
    $qb->setParameter(1, $value);

    return $qb->getQuery()
        ->getResult();
}

Then, I could call it in my controller like this:

$this->getDoctrine()->getRepository('MyBundle:Image')->findByNot('id', 1);

answered May 24 '13 at 21:16

Luis

706
9
9

Can you please tel me where to add this method? – Dency G B Jan 22 '14 at 04:27
1

In a custom repository, related to the entity. In symphony, to access data you use a default repository, you can define your own extending to it. Read more at [the doctrine docs](http://symfony.com/doc/current/book/doctrine.html#custom-repository-classes) – Luis Mar 11 '14 at 23:45

score 16 · Answer 4 · edited Oct 17 '19 at 18:58

Based on the answer from Luis, you can do something more like the default findBy method.

First, create a default repository class that is going to be used by all your entities.

/* $config is the entity manager configuration object. */
$config->setDefaultRepositoryClassName( 'MyCompany\Repository' );

Or you can edit this in config.yml

doctrine: orm: default_repository_class: MyCompany\Repository

Then:

<?php

namespace MyCompany;

use Doctrine\ORM\EntityRepository;

class Repository extends EntityRepository {

    public function findByNot( array $criteria, array $orderBy = null, $limit = null, $offset = null )
    {
        $qb = $this->getEntityManager()->createQueryBuilder();
        $expr = $this->getEntityManager()->getExpressionBuilder();

        $qb->select( 'entity' )
            ->from( $this->getEntityName(), 'entity' );

        foreach ( $criteria as $field => $value ) {
            // IF INTEGER neq, IF NOT notLike
            if($this->getEntityManager()->getClassMetadata($this->getEntityName())->getFieldMapping($field)["type"]=="integer") {
                $qb->andWhere( $expr->neq( 'entity.' . $field, $value ) );
            } else {
                $qb->andWhere( $expr->notLike( 'entity.' . $field, $qb->expr()->literal($value) ) );
            }
        }

        if ( $orderBy ) {

            foreach ( $orderBy as $field => $order ) {

                $qb->addOrderBy( 'entity.' . $field, $order );
            }
        }

        if ( $limit )
            $qb->setMaxResults( $limit );

        if ( $offset )
            $qb->setFirstResult( $offset );

        return $qb->getQuery()
            ->getResult();
    }

}

The usage is the same than the findBy method, example:

$entityManager->getRepository( 'MyRepo' )->findByNot(
    array( 'status' => Status::STATUS_DISABLED )
);

Emery King · Answer 5 · 2023-02-01T15:57:54.507

I solved this rather easily (without adding a method) so i'll share:

use Doctrine\Common\Collections\Criteria;

$repository->matching( Criteria::create()->where( Criteria::expr()->neq('id', 1) ) );

By the way, i'm using the Doctrine ORM module from within Zend Framework 2 and i'm not sure whether this would be compatible in any other case.

In my case, i was using a form element configuration like this: to show all roles except "guest" in a radio button array.

$this->add([
    'type' => 'DoctrineModule\Form\Element\ObjectRadio',
    'name' => 'roles',
    'options' => [
        'label' => _('Roles'),
        'object_manager' => $this->getEntityManager(),
        'target_class'   => 'Application\Entity\Role',
        'property' => 'roleId',
        'find_method'    => [
            'name'   => 'matching',
            'params' => [
                'criteria' => Criteria::create()->where(
                    Criteria::expr()->neq('roleId', 'guest')
                )
            ],
        ],
    ],
]);

You are the man! I've been looking for this for ages. Did you find anything about this in the docs? — halfpastfour.am, Nov 07 '17 at 09:22
Thanks for that, I'm sure other people are looking for this as well. — halfpastfour.am, Nov 08 '17 at 19:03

score 2 · Answer 6 · answered Nov 25 '19 at 12:35

2

I used the QueryBuilder to get the data,

$query=$this->dm->createQueryBuilder('AppBundle:DocumentName')
             ->field('fieldName')->notEqual(null);

$data=$query->getQuery()->execute();

answered Nov 25 '19 at 12:35

Abhi Das

500
8
11

Doctrine findBy 'does not equal'

6 Answers6

Linked