CODEWITHSUNDEEP
sqldoctrinedqlrelation
JHGitty
JHGitty

Reputation: 23

Get all entries if no entries in linked table with condition (Doctrine2, Symfony2)

There are 2 tables: Providers and Adverts. A Provider has Adverts.

First table "Provider":

Second table "Advert":

Relation:

 /**
 * @ORM\OneToMany(targetEntity="Advert", mappedBy="provider", cascade={"persist"})
 */
private $adverts;

I want:

All Providers who DON'T have any adverts which are currently active (= currently between "Begin" and "End") AND DON'T have any adverts which are planned for the future (= "Begin" and "End" are in the future).

In other words:

I want ALL Providers who DON'T have any current or upcoming adverts.

My issue:

I don't know and find any information how to do it.

I use Doctrine2 with Symfony 2.8 / 3.0.

Upvotes: 1

Views: 83

Answers (2)

stevenll
stevenll

Reputation: 1025

Please note that some MySQL specific funtions are not supported by default in Doctrine. You can have an extension installed for that. I am going to propose a way without extensions. (But you should opt out for a Bundle on your own judgement)

In your Provider repository you can do something like this: 

public function getProvidersWithoutAdverts()
{
    $now = date("Y-m-d H:i:s");
    $qb = $this->createQueryBuilder('provider');

    $qb->leftJoin('provider.adverts', 'advert', Join::WITH)
       ->where("advert.end < {$now}") //Advert  has ended
       ->andWhere("advert.begin < {$now}") //Advert is not scheduled
       ;

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

}

Upvotes: 1

JHGitty
JHGitty

Reputation: 23

This code works fine but maybe it is not fully optimized.

Warning: You should create a repository for the Provider entity. The following code is working if you have $em as Doctrine EntityManager.

    $qb = $em->createQueryBuilder();
    $qb = $qb->select('IDENTITY(advert.provider)')
        ->from('AppBundle:Advert', 'advert')
        ->where("advert.begin <= :now AND advert.end > :now")
        ->andWhere('advert.active = true')
        ->setParameter(':now', new \DateTime(), Type::DATETIME)

    $nots = $qb->getQuery()->getArrayResult();

    $qb = $em->createQueryBuilder();
    $qb = $qb->select('p')
        ->from('AppBundle:Provider', 'provider')
        ->leftJoin('provider.adverts', 'advert');
    if (isset($nots[0])) {
        $qb->where($qb->expr()->notIn('provider.id', $nots[0]));
    }

    $providers = $qb->getQuery()->getArrayResult();

Maybe "not exists" would be better: NOT IN vs NOT EXISTS

Upvotes: 0

Related Questions