CODEWITHSUNDEEP
symfonydoctrineentity-relationshipquery-builder
justme
justme

Reputation: 43

QueryBuilder with One To Many relationship and several conditions using Doctrine

I will try to explain my issue as well as I can. I have a Message related to another Entity called Reply (one message can have zero or n replies) and I want to take into account the following cases:

  1. If UserA have one message created to UserB (UserA is the creator) but the UserB didn't replied the message I don't want get message
  1. Same as above but the UserB replied to the message. I want get the message with its replies if replies exists
  1. If the UserB sent a message to UserA, I want to get the message with its replies if replies exists

My Message Entity (I will just put the OneToMany relantionship):

<?php
namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;


/**
* @ORM\Table(name="messages")
* @ORM\Entity
* @ORM\Entity(repositoryClass="AppBundle\Repository\MessageRepository")
*/
class Message {

/**
* @ORM\OneToMany(targetEntity="ReplyMessage", mappedBy="message")
*/
private $replies;

/**
 * Add replies
 *
 * @param \AppBundle\Entity\ReplyMessage $replies
 * @return Message
 */
public function addReply(\AppBundle\Entity\ReplyMessage $replies)
{
    $this->replies[] = $replies;

    return $this;
}

/**
 * Remove replies
 *
 * @param \AppBundle\Entity\ReplyMessage $replies
 */
public function removeReply(\AppBundle\Entity\ReplyMessage $replies)
{
    $this->replies->removeElement($replies);
}

/**
 * Get replies
 *
 * @return \Doctrine\Common\Collections\Collection 
 */
public function getReplies()
{
    return $this->replies;
}

And the code that I am using to achieve my objective is the following:

$query = $this->createQueryBuilder('message')
    ->where('message.creator = :username or message.receiver = :username')
    ->leftJoin('message.replies', 'replies')
    ->andWhere('replies.user = :username')
    ->setParameter('username', $username)
    ->getQuery();

$sent = $query->getResult();

return $sent;

I am not an expert building queries with Doctrine and I don't know how can control all my cases right now, if someone could help I'd be very grateful

Upvotes: 2

Views: 17264

Answers (2)

Maxim Strutinskiy
Maxim Strutinskiy

Reputation: 531

Don't use ->getResult(); in you AppBundle\Repository\MessageRepository repository, becose sometimes you need use him meny times, and for example: BreadcrumbsBundle need query (not result, еhis is affected by performance)

Upvotes: -1

Herr Nentu&#39;
Herr Nentu&#39;

Reputation: 1506

That's the beauty of QueryBuilders. You can create statements like this:

$query = $this->createQueryBuilder('message')
    ->where('message.creator = :username or message.receiver = :username')
    ->leftJoin('message.replies', 'replies')
    ->andWhere('replies.user = :username')
    ->setParameter('username', $username);

if (some_condition) {
    $query->andWhere('some_property');
}

if (some_other_condition) {
    $query->andWhere('some_property');
}

return $query->getQuery()->getResults();

Upvotes: 4

Related Questions