Implementing a friends list in Symfony2.1 with Doc

Question

I want to implement friends list of particular user in Symfony2.1 and Doctrine. Lets say friends table:

User1 User2 Status     //0-pending request,1-accepted
A     B      0
A     C      1
D     A      1
E     A      1

Now I want to get A's friends name in the list. For this SQL query can be implemented using UNION as read in many other answers. But I want to implement this in doctrine query builder. One option is like query separately for two columns and combine the result and sort. But this takes more time to execute and get result. I want to get quick response as soon as possible. Is there any way to query it?

Answer 1

You don't need any additional effort, e.g. by using Doctrine Query Builder!

Simply design the entity class User to have a many-to-many self-reference with User, e.g.:

 * @ORM\Table()
 * @ORM\Entity()
 */
class User
{
....

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

    /**
     * @ORM\ManyToMany(targetEntity="User", mappedBy="myFriends")
     **/
    private $friendsWithMe;

    /**
     * @ORM\ManyToMany(targetEntity="User", inversedBy="friendsWithMe")
     * @ORM\JoinTable(name="friends",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="friend_user_id", referencedColumnName="id")}
     *      )
     **/
    private $myFriends;

    public function __construct() {
        $this->friendsWithMe = new \Doctrine\Common\Collections\ArrayCollection();
        $this->myFriends = new \Doctrine\Common\Collections\ArrayCollection();
    }

}

Then you can simply get the User entity and obtains all the friends as follows:

$user = $this->getDoctrine()
                ->getRepository('AcmeUserBundle:User')
                ->findOneById($anUserId);
$friends = $user->getMyFriends();
$names = array();
foreach($friends as $friend) $names[] = $friend->getName();