How to build query from SQL by Doctrine QueryBuilder?

Question

I'm new in Doctrine ORM, and I need help with building QueryBuilder command. I have a SQL command and I need covert to QueryBuilder.

$qb = $this->entityManager->createQueryBuilder();

The SQL is:

SELECT `user`.*
FROM `user`
JOIN `friends` ON `user`.`id` != $uid AND  (`user`.`id` = `friends`.`origin` OR  `user`.`id` = `friends`.`destination`)
WHERE `friends`.`origin` = $uid OR `friends`.`destination` = $uid;

`user` is App\Model\Database\Entity\User and `friends` is App\Model\Database\Entity\Friends

The $uid is a user whose friends I want to see. As result of query I need array of User entites.

Friends is simple table with unique ID and with origin and destination, which are User IDs, when I want to ask someone for friendship, origin will be my UID and destination will be his UID. This function above is for getting users friends, no matter who called for friendship.

Can someone help me please?

Answer 1

I guess you could convert your SQL to DQL and it will look similar to

SELECT u
FROM App\Model\Database\Entity\User  u
JOIN App\Model\Database\Entity\Friends f WITH u.id != :uid 
    AND  (u.id = f.origin OR  u.id = f.destination)
WHERE f.origin = :uid OR f.destination = :uid

and in query builder you can write it as

$em->createQuery('
        SELECT u
        FROM App\Model\Database\Entity\User  u
        JOIN App\Model\Database\Entity\Friends f WITH u.id != :uid 
            AND  (u.id = f.origin OR  u.id = f.destination)
        WHERE f.origin = :uid OR f.destination = :uid'
        )->setParameters(array(
            'uid' => $uid
        ));