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

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[![enter image description here][1]][1]

[![enter image description here][2]][2]


[1]:

[2]:

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

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.

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.

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**.

You can:

- Inner join with the languages you want
- use [aggregate functions][1] 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][2] 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:

<strike>->addSelect('a')</strike>

you will have to do another query.

[1]:

[To see links please register here]

[2]:

[To see links please register here]