How to make a UNION with Doctrine?

Question

I'm trying to do the following query:

    public function findByNotifications($ownerId)
    {
        $em = $this->getEntityManager();
        $query = $em->createQuery('
           SELECT n FROM
            (SELECT n FROM DelivveWebBundle:UserAd n
                INNER JOIN n.ad ad
                    WHERE ad.owner = :ownerId
                LIMIT 20
            UNION
            SELECT n FROM DelivveWebBundle:UserAd n
                INNER JOIN n.user u
                INNER JOIN n.ad ad
                   WHERE u.id = :ownerId
                       AND ad.status = :progress
                LIMIT 20)
           notofication
           LIMIT 20;
        ')->setParameter('ownerId', $ownerId)
            ->setParameter('progress', Constant::AD_IN_PROGRESS);

        $result = $query->getResult();

        return $result;
    }

to generate all my notifications:

    public function showNotificationsAction()
    {
        $this->denyAccessUnlessGranted('ROLE_USER', null, 'Unable to access this page!');

        $owner = $this->getUser();

        $repository = $this->getDoctrine()->getRepository('DelivveWebBundle:UserAd');

        $notifications = $repository->findByAdOwner($owner->getId());

        return $this->render('DelivveWebBundle:Ad:notification.html.twig', array(
            'owner' => $owner,
            'notifications' => $notifications
        ));
    }

The idea is to do a search on AdUser table that returns all notifications that have ads that logged User owns, along with any notifications that logged User requested.

Notification the User requested is a line of AdUser table that has the column the user logged in User.

The Doctrine Query Language does not support the key word `UNION`. A workaround can be found at http://stackoverflow.com/questions/4155288/how-to-write-union-in-doctrine-2-0 — Max Scott, Sep 03 '15 at 20:00
This will be a problem, that you know will be some way to do what I want? The response from the other pergunda not ultimately help me soulação it was breaking his table in two and I do not want it, just want to generate a list with the two queries. — Marcius Leandro, Sep 03 '15 at 20:05
Are you wanting the first 20 notifications where the logged User owns the ad, followed by the first 20 notifications that the logged User requested? If so, I don't understand why you would want this result then limited to the first 20 notifications of these notifications (your last `LIMIT`). Is 'notofication` meant to be `n`? — Max Scott, Sep 03 '15 at 20:32
Then the limit is to pick 20 first notifications of a kind and of another 20 and then in the outer select cut to 20 after 20 of each will have 40, is that this missing an order by update date not yet put — Marcius Leandro, Sep 03 '15 at 20:36
SInce you are limiting yourself to 20 notifications ordered by update date, limiting the sub SELECTs to 20 notifications is not required (it makes no difference if the limit is there or not). (It would make a difference if your final ordering is different from the ordering in the sub SELECTs.) You could use a single SELECT placing an OR in the WHERE clause. — Max Scott, Sep 03 '15 at 20:53

score 2 · Accepted Answer · answered Sep 03 '15 at 21:05

I decided to breaking in two searches and giving a marge in results

public function findByAdOwner($ownerId)
{
    $qb = $this->getEntityManager()->createQueryBuilder('n');

    return $qb->select('n')
        ->from('DelivveWebBundle:UserAd', 'n')
        ->join('n.ad', 'ad')
        ->where('ad.owner = :ownerId')
        ->setParameter('ownerId', $ownerId)
        ->setMaxResults(20)
        ->getQuery()
        ->getResult();
}

public function findByUserNotify($userId)
{
    $qb = $this->getEntityManager()->createQueryBuilder('n');

    return $qb->select('n')
        ->from('DelivveWebBundle:UserAd', 'n')
        ->join('n.ad', 'ad')
        ->where('n.user = :userId')
        ->andWhere('ad.status = :status')
        ->setParameter('userId', $userId)
        ->setParameter('status', Constant::AD_IN_PROGRESS)
        ->setMaxResults(20)
        ->getQuery()
        ->getResult();
}

public function findNotifcations($userId){
    $notification = $this->findByAdOwner($userId);
    $append = $this->findByUserNotify($userId);

    return array_merge($notification, $append);
}

To become more readable'll just put after something that distinguishes the two types of notice to do the treatment on the page.

I discovered that there is a way to add commands to the doctrine that does not exist, but appears to be quite complex if anyone knows do this, put the answer please.

How to make a UNION with Doctrine?

1 Answers1

Linked