Doctrine 2 QueryBuilder

Assuming the following two tables, 'user' and 'friends';

'user'
column: id
column: name

'friends'
column: user_id
column: user2_id

Both columns in the friends table correspond to the user table id column.

Now I can simply find users by partial name with the following;

$query='jim';
$result=$em->getRepository('\User\Entity\User')
        ->createQueryBuilder('u')
        ->where('u.name like :match')
        ->setParameter('match', $query.'%')
        ->setMaxResults(5)
        ->getQuery()
        ->getResult();

Now assuming an object of \User\Entity\User userA, how would I do a partial string match for all users that userA is not friends with already ?

EDIT Added the Entity definitions

/**
 * User
 *
 * @ORM\Table(name="user", uniqueConstraints={@ORM\UniqueConstraint(name="name_key", columns={"name"})})
 * @ORM\Entity
 */
class User
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="bigint", precision=0, scale=0, nullable=false, unique=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, precision=0, scale=0, nullable=false, unique=false)
     */
    private $name;
}


/**
 * UserFriends
 *
 * @ORM\Table(name="user_friends", indexes={@ORM\Index(name="user_id_key", columns={"user_id"}), @ORM\Index(name="friend_user_id_key", columns={"friend_user_id"})})
 * @ORM\Entity
 */
class UserFriends
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="bigint", precision=0, scale=0, nullable=false, unique=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var \User\Entity\User
     *
     * @ORM\ManyToOne(targetEntity="User\Entity\User")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="friend_user_id", referencedColumnName="id", nullable=true)
     * })
     */
    private $friendUser;

    /**
     * @var \User\Entity\User
     *
     * @ORM\ManyToOne(targetEntity="User\Entity\User")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=true)
     * })
     */
    private $user;
}`

Solution

We’ll do it in two steps. First, we find out which users they are friends with, then we do a query excluding them.

First, we retrieve an ID list of the user’s friends:

$friendIds = $em->createQuery("
    SELECT IDENTITY(uf.user)
    FROM User\Entity\UserFriends uf
    WHERE uf.user = :id")
->setParameter("id", $userId) // $userId is the ID of the target user
->getResult();

$friendIds = array_map("current", $friendIds); // needed for flattening the array

Now we simply query the user table, excluding the IDs of our friends:

$notMyFriends = $em->createQuery("
    SELECT u
    FROM User\Entity\User u
    WHERE u.id != :ownid 
    AND WHERE u.id NOT IN (:friends)")
->setParameter("ownid", $userId) // $userId is the ID of the target user
->setParameter("friends", $friendIds)
->getResult();

I’m using plain DQL here, but if you prefer the query builder, you can as well rewrite it to a QB method chain.

(All of the above is from the top of my head, hope it works. If not, please leave a comment and I'll try to fix it.)

By the way, please let me give you a few hints regarding your entity classes:

Entity classes should always be in singular UserFriends → UserFriend.
all of your name="foobar" annotation parameters are redundant, as Doctrine will auto-name your tables, indices and columns.
Same goes for the JoinColumns … you can omit them altogether, unless you want to explicitely change the default behaviour (which you usually don’t).
nullable=true is the default for an xToOne relation, so it can be omitted, too.
nullable=false and unique=false don’t make sense on an ID field. The first is redundant, the second is impossible.