Return ORM object and convert SQL to Doctrine QueryBuilder syntax-CodePudding

I'm creating a function that should return an array of User ORM object. The function should run a query to the DB and return the users where the users' contact persons has 1 company (not more or less). The relationship is like this: every user has one or more contact person and every contact person has one or more companies.

The SQL to locate these users are like this. We are using PHP 7.1, Symfony 3.4 and Doctrine 2.7.

The problem that I have is that I cannot manage to describe this in Doctrine QueryBuilder syntax so that an array of User ORM objects are returned. Can anybody give me some advice?

SELECT users.email
FROM company
         INNER JOIN contact_person ON contact_person.id = company.belongs_to_contact_person_id
         INNER JOIN users ON users.id = contact_person.belongs_to_user_id
GROUP BY users.email
HAVING COUNT(company.id) = 1

CodePudding user response：

Depending on how your mapping is on your entities, you have multiple solution.

It would be nice if you can show us what you tried so we can see what you miss.

The best is to use the repository of the entity you whish to have an array of:

namespace App\Repository;

use App\Entity\User;
use Doctrine\ORM\EntityRepository;

class UserRepository extends EntityRepository
{

    /**
     * @return User[]
     */
    public function findUsersHavingAtLeastOneCompany():array
    {
        return $this->createQueryBuilder('user')
            ->join('user.contact', 'contact')
            ->join('contact.company', 'company')
            ->where('contact.company = 1')
            ->getQuery()
            ->getResult();
    }
}

When using the createQueryBuilder function, it will auto populate the select and the from. The getResult will return an array of entity (if you have not defined a select)

CodePudding user response：

I fixed this by using the following code using createNativeQuery. It can probably be done by using fever lines of code, but it does the job for me :)

    $em = $this->getEntityManager();

        $sql = <<<SQL
        SELECT users.id
        FROM company
        INNER JOIN contact_person ON contact_person.id = company.belongs_to_contact_person_id
        INNER JOIN users ON users.id = contact_person.belongs_to_user_id
        GROUP BY users.id
        HAVING COUNT(company.id) = 1
SQL;
        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('id', 'text');
        $query = $em->createNativeQuery($sql, $rsm);

        $locatedUsers = [];
        foreach ($query->getResult() as $lUser) {
            foreach ($lUser as $user) {
                $locatedUser = $em->find("Project\User\User", $user);
                array_push($locatedUsers, $locatedUser);
            }
        }

        return $locatedUsers;