How to map a double Many To One relationship to the same table in Doctrine 2?

Question

I have two tables created.

Players               Message
________              _________
id      -----------   id
                   |
username           -- sender_id
                   |
password           -- receiver_id

                      sent_on

                      text

The Entities I have so far are:

AppBundle\Entity\Message

class Message
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="text", type="text")
 */
private $text;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="sent_on", type="datetime")
 */
private $sentOn;


/**
 * Get id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

/**
 * Set text
 *
 * @param string $text
 *
 * @return Message
 */
public function setText($text)
{
    $this->text = $text;

    return $this;
}

/**
 * Get text
 *
 * @return string
 */
public function getText()
{
    return $this->text;
}

/**
 * Set sentOn
 *
 * @param \DateTime $sentOn
 *
 * @return Message
 */
public function setSentOn($sentOn)
{
    $this->sentOn = $sentOn;

    return $this;
}

/**
 * Get sentOn
 *
 * @return \DateTime
 */
public function getSentOn()
{
    return $this->sentOn;
}

AppBundle\Entity\Player

class Player implements UserInterface
{
/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="username", type="string", length=100, unique=true)
 */
private $username;

/**
 * @var string
 *
 * @ORM\Column(name="email", type="string", length=255, unique=true)
 */
private $email;

/**
 * @var string
 *
 * @ORM\Column(name="password", type="string", length=255)
 */
private $password;


/**
 * Get id
 *
 * @return int
 */
public function getId()
{
    return $this->id;
}

/**
 * Set username
 *
 * @param string $username
 *
 * @return Player
 */
public function setUsername($username)
{
    $this->username = $username;

    return $this;
}

/**
 * Get username
 *
 * @return string
 */
public function getUsername()
{
    return $this->username;
}

/**
 * Set password
 *
 * @param string $password
 *
 * @return Player
 */
public function setPassword($password)
{
    $this->password = $password;

    return $this;
}

/**
 * Get password
 *
 * @return string
 */
public function getPassword()
{
    return $this->password;
}

/**
 * Returns the roles granted to the user.
 *
 * <code>
 * public function getRoles()
 * {
 *     return array('ROLE_USER');
 * }
 * </code>
 *
 * Alternatively, the roles might be stored on a ``roles`` property,
 * and populated in any number of different ways when the user object
 * is created.
 *
 * @return (Role|string)[] The user roles
 */
public function getRoles()
{
    return ['ROLE_USER'];
}

/**
 * Returns the salt that was originally used to encode the password.
 *
 * This can return null if the password was not encoded using a salt.
 *
 * @return string|null The salt
 */
public function getSalt()
{
    return null;
}

/**
 * Removes sensitive data from the user.
 *
 * This is important if, at any given point, sensitive information like
 * the plain-text password is stored on this object.
 */
public function eraseCredentials()
{

}

}

How should I connect these two tables? Is the connection ManyToMany or just ManyToOne <-> OneToMany? I want to achieve the same as

SELECT *
FROM messages, players
WHERE (players.id = messages.sender_id OR players.id = messages.receiver_id) AND players.id = 1

Answer 1

ManyToMany or ManyToOne?

It's clearly a ManyToOne scenario, in a ManyToMany scenario you would have a third table, an intermediate table.

How connect theses two tables

class Message
{

   //columns here
   //...

   /**
    * @ORM\ManyToOne(target="Player", inversedBy="receivedMessages")
    * @ORM\JoinColumn(name="receiver_id",referencedColumnName="id")
    */
   protected $receiver;

   /**
    * @ORM\ManyToOne(target="Player", inversedBy="sentMessages")
    * @ORM\JoinColumn(name="sender_id",referencedColumnName="id")
    */
   protected $sender;

   // create getters & setters

}

class Player implements UserInterface
{
    //columns here
    //...

   /**
    * @ORM\OneToMany(target="Message", mappedBy="receiver")
    */
    protected $receivedMessages;

   /**
    * @ORM\OneToMany(target="Message", mappedBy="sender")
    */
    protected $sentMessages;

    // create getters & setters

}

SQL to DQL

SQL:

SELECT *
 FROM messages, players
 WHERE (players.id = messages.sender_id OR players.id = messages.receiver_id) 
  AND players.id = 1

Notice, in DQL you need to think in OOP (which is usually a much more natural thought):

 SELECT m, s, r
  FROM \AppBundle\Entity\Message m
  INNER JOIN m.sender s
  INNER JOIN m.receiver r
  WHERE s.id = 1 OR r.id = 1