How to Write Union in Doctrine 2.0

How to write UNION in Doctrine 2.0

Well, I found maybe the best solution:

/**
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"group" = "NotificationGroup", "event" = "NotificationEvent"})
 */
class Notification {
   // ...
}

And then two classes (NotificationGroup and NotificationEvent) extending Notification:

/**
 * @Entity
 */
class NotificationGroup extends Notification {
    //...
}

/**
 * @Entity
 */
class NotificationEvent extends Notification {
    //...
}

How to make a UNION with Doctrine?

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.

SQL query with UNION in Doctrine Symfony

UNION is not supported within DQL, but you can issue your query using RAW SQL ->

$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute(" -- RAW SQL HERE -- ");

Concataine 2 doctrine repository in a single variable

findAll() function will return array. I think you can merge it using array_merge function.

$prints = $repositoryPrints->findAll();
$files = $repositoryFiles->findAll();

$showAll = array_merge($prints, $files);

Implement SQL Union Query using Query Builder in Doctrine Symfony2

I don't know how stupid I am when joining 2 queries without checking the conditions.
This is the correct query :

SELECT m.id, m.subject, m.date
FROM message m 
JOIN message_incoming mi ON m.id = mi.id
JOIN message_outgoing mo ON m.id = mo.id
WHERE m.recipient_id = 1
AND mi .trash = 1
AND mi .deleted = 0
OR m.originator_id = 1
AND mo .trash = 1
AND mo .deleted =0
AND mo .sent = 1
ORDER by date DESC

I tried implement it through query builder :

$queryBuilder = $this->entityManager->getRepository('EzxWebmailBundle:Message')
            ->createQueryBuilder('m')
            ->select('m.id','m.subject','m.date')
            ->join('m.message_outgoing','mo','ON','m.id = mo.id')
            ->join('m.message_incoming','mi','ON','m.id = mi.id')
            ->where('m.recipient_id = '.$userId)
            ->andWhere('mi.trash = 1')
            ->andWhere('mi.deleted = 0')
            ->orWhere('m.originator_id = '.$userId)
            ->andWhere('mo.trash = 1')
            ->andWhere('mo.deleted = 0')
            ->andWhere('mo.sent = 1')
            ->orderBy('m.date','DESC');
$result = $queryBuilder->getQuery()->getResult();

How surprisingly it returns incorrect result ! So i tried to see what query was generated using :

var_dump($queryBuilder->getQuery());

And I really don't know why doctrine generates extra parenthesis as I get this result (carefully have a look at WHERE clause) :

SELECT m.id, m.subject, m.date FROM message m 
INNER JOIN message_outgoing mo ON m.id = mo.id
INNER JOIN message_incoming mi ON m.id = mi.id
WHERE ((m.recipient_id = 1 AND mi.trash = 1 AND mi.deleted = 0) OR m.originator_id = 1) AND mo.trash = 1 AND mo.deleted = 0 AND mo.sent = 1 
ORDER BY m.date DESC

So this must be the correct one if I add my own parenthsis :

$queryBuilder = $this->entityManager->getRepository('EzxWebmailBundle:Message')
            ->createQueryBuilder('m')
            ->select('m.id','m.subject','m.date')
            ->join('m.message_outgoing','mo','ON','m.id = mo.id')
            ->join('m.message_incoming','mi','ON','m.id = mi.id')
            ->where('(m.recipient_id = '.$userId)
            ->andWhere('mi.trash = 1')
            ->andWhere('mi.deleted = 0)')
            ->orWhere('(m.originator_id = '.$userId)
            ->andWhere('mo.trash = 1')
            ->andWhere('mo.deleted = 0')
            ->andWhere('mo.sent = 1)')
            ->orderBy('m.date','DESC')

Feel a little stupid.

Best approach to retrieve entity field form from database

I'm not sure if I get you right. To show All tags in the twitterpost form you are already doing the right thing:

    ->add('tags', 'entity', array(
          'class' =>    'FEBTagsBundle:tag',
          'property' => 'tag',
          'empty_value' => 'Selecciona tags',
          'multiple' => true
));

This will create a multiple select box where you can select from all the tags. From the docs:

property

type: string

This is the property that should be used for displaying the entities as text in the HTML element. If left blank, the entity object will be cast into a string and so must have a __toString() method.

If you don't want a multi-select but checkboxes or radiobuttons instead, checkout this part of the documentation.

---

In case you are asking on how to output the data when not inside the TwitterpostType, you can just treat them as objects in a Controller:

$repository = $this->getDoctrine()->getRepository('TwitterBundle:Twitterpost');
$post = $repository->findOne($id);

// to get all _tags_ of a post just call the getter:
$tags = $post->getTags();

The same goes for twig templates:

{# imagine you have all posts in a variable called 'posts' #}
{% for post in posts %}
     <h1>{{ post.titulo }}</h1>
     <div class="tags">
         {% for tag in posts.tags %}
         {{ tag.tag }}
         {% endfor %}
     </div>
{% endfor %}

---

Edit:

For a native SQL / DQL query you need a custom repository class. There you can store your own queries:

class TwitterpostRepository extends EntityRepository
{
    public function findAllOrderedByName()
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT p FROM TwitterBundle:Twitterpost p ORDER BY p.name ASC'
            )
            ->getResult();
    }
}

Then you can use it like this in the controller:

$repository = $this->getDoctrine()->getRepository('TwitterBundle:Twitterpost');
$post = $repository->findAllOrderedByName();

Doctrine 2 - One-to-Many unidirectional

You were on the 2.0.x version of the documentation. Check this one. You will have the example.

So yes, you can avoid the annotation in one of the two classes.

---

Related Topics