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.