How to fetch (join) two records from database using doctrine/symfony4

Question

I am learning about Symfony and Doctrine and created a simple site but I am stuck at this step.

I have two tables: users and languages

Users Contains: id, username ...
Languages Contains: user_id, language...

Here is a image of the two

Now I am trying to fetch by language, like: get user who speaks both english and french and the result would return user id 2

In plain PHP i can do inner join with PDO, but I am trying to follow the doctrine syntax and this does not return the correct result

public function getMatchingLanguages ($a, $b) {
  return $this->createQueryBuilder('u')
    ->andWhere('u.language = :val1 AND u.language = :val2')
    ->setParameter('val1', $a)
    ->setParameter('val2', $b)
    ->getQuery()
    ->execute();
}

I call this method in my controllers, and the query is pretty basic since I can not find a documentation how to do the joins as per my example

Answer 1

In your User model add next code:

/**
 * @ORM\OneToMany(targetEntity="Language", mappedBy="user", fetch="EXTRA_LAZY")
 */
public $languages;

In your Language model add next code:

/**
 * @ORM\ManyToOne(targetEntity="User", inversedBy="languages")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="user_id", referencedColumnName="id")
 * })
 */
public $user;

By this way you define simple One-To-Many relation between User and Language, but it is not enough for getting your user that support both languages. You need to make 2 joins of user table and language table. That's how it looks like (if you use controller):

  $user = $this->get('doctrine')
        ->getEntityManager()
        ->createQueryBuilder()
        ->select('u')
        ->from(User::class, 'u')
        ->join('u.languages', 'l_eng', 'WITH', 'l_eng.language = :engCode')
        ->join('u.languages', 'l_fr', 'WITH', 'l_fr.language = :frCode')
        ->setParameters([
            'engCode' => 'english',
            'frCode' => 'french'
        ])
        ->getQuery()->execute();

Or, if you use UserRepository class (most preferable):

public function findAllByLangs()
{
    return $this->createQueryBuilder('u')
        ->join('u.languages', 'l_eng', 'WITH', 'l_eng.lang = :engCode')
        ->join('u.languages', 'l_fr', 'WITH', 'l_fr.lang = :frCode')
        ->setParameters([
            'engCode' => 'english',
            'frCode' => 'french'
        ])
        ->getQuery()->execute();
}

So main trick is join language table with condition of english to filter users, that support english language AND join language table again but with french in "ON" section to filter users who support french language as well.

Answer 2

By analyzing your DB tables, I assume that your Entities are like this

// User.php

class User implements UserInterface
{
    /**
     * @ORM\Column(type="guid")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="UUID")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=100)
     */
    private $username;
}

// Language.php

class Language
{

    /**
     * @ORM\Column(type="guid")
     */
    private $userId;

    /**
     * @ORM\Column(type="string", length=30)
     */
    private $language;
}

If you have the same setup (as above Entities), then you can write your query like this in UserRepository.php

public function getUsersForMatchingLanguages ($langOne, $langTwo) {
    return $this->createQueryBuilder('user')
        ->select('user.id, user.username, language.language')
        ->innerJoin(Language::class, 'language', 'WITH', 'language.user_id = user.id')
        ->where('language.language = :langOne AND language.language = :langTwo')
        ->setParameter('langOne ', $langOne )
        ->setParameter('langTwo', $langTwo)
        ->getQuery()
        ->getResult();
}

This will return you array of results.

Answer 3

Maybe I am not understand question correctly, please correct me if I am wrong, but if you need user(s) that speaks BOTH languages you have an error in SQL logic not in doctrine. You should do smth like:

SELECT * FROM user u JOIN language l ON u.id = l.user_id AND l.language = 'english' JOIN language l2 ON u.id = l2.user_id AND l2.language = 'french' GROUP BY u.id;

If query correct for you I can write DQL interpretation for it.

Answer 4

You can:

• Inner join with the languages you want
• use aggregate functions to count the joined results(joined languages)
• group by the user entity
• filter the results for count(lang) = 2

Code:

use Doctrine\ORM\Query\Expr\Join;

public function getMatchingLanguages ($a, $b) {
    return $this->createQueryBuilder('u')
                ->addSelect('COUNT(a) as HIDDEN total')
                ->innerJoin('u.languages', 'a', Join::WITH, 'a.language = :val1 OR a.language = :val2')
        ->groupBy('u');
                 ->having('total = :total') //or ->having('COUNT(a) = :total')
        ->setParameter('total', 2)
        ->setParameter('val1', $a)
        ->setParameter('val2', $b)
        ->getQuery()
        ->execute();
}

$this->getMatchingLanguages('english', 'french');

This works by inner joining user only with rows with english or french and then using mysql having to "check" if we got 2 rows for each user.

If you want also the Languages entities hydrated to your result, you cannot add it to the querybuilder result since you group by:

->addSelect('a')

you will have to do another query.