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

Question

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

First table "Provider":

ID
...

Second table "Advert":

ID
Begin (DateTime)
End (DateTime)
...

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.

Can you add how Provider is Related to Advert? Which is the owning side in your mapping etc — stevenll, Oct 26 '15 at 12:12

stevenll · Answer 1 · 2015-11-04T13:53:35.907

1

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();

}

edited Nov 04 '15 at 13:53

answered Oct 26 '15 at 12:58

stevenll

1,025
12
29

The left join return even providers who have no advertisements at all – stevenll Oct 26 '15 at 14:52
Firstly, you basically want all Providers who can be interested in an Advert. `leftJoin` assures you to fetch Providers who have never had an Advert. Also, in order for the Advert not to be Active, it means that it's end is in the past (it has ended) and for it not to be a scheduled one, it's start should not be in the future. So, you get all the providers who, yes have had Advert history but no active or scheduled ones. This is what you wanted no? – stevenll Oct 26 '15 at 15:20
I don't want a provider if it has currently adverts. I don't want a provider if it will have adverts in the future (planned adverts). I ONLY want the providers which don't have any current or upcoming adverts. I also want the provider if it never had any adverts. – JHGitty Oct 27 '15 at 08:01
If I have 1 advert in the past and 1 active advert I'' get the provider with your code. That is wrong. I don't want that provider (because it currently has an active advert). That because your current code is wrong. – JHGitty Oct 27 '15 at 08:49
But, if your active advert has an end date greater than now, it wouldn't pass `where("advert.end < {$now}")`. – stevenll Oct 27 '15 at 12:06

score 0 · Accepted Answer · edited May 23 '17 at 12: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

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

2 Answers2