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

doctrine join
symfony doctrine join
symfony doctrine left join
doctrine join table
symfony database config
doctrine join multiple tables
doctrine join subquery
symfony get data from database

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

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.

Query across a JOIN (and Love it) > Mastering Doctrine , What about a JOIN query with Doctrine? Well, they're really Get to work on that project or keep going with me to learn more Symfony! I promise, more bad  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

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.

Databases and the Doctrine ORM (Symfony Docs), Symfony provides all the tools you need to use databases in your applications than Persisting Objects to the Database; Validating Objects; Fetching Objects from src/Entity/Product.php namespace App\Entity; use Doctrine\ORM\Mapping as Each time you make a change to your schema, run these two commands to​  Fetching Objects from the Database using Doctrine and Symfony CRUD stands for Create, Read, Update and Delete, the basic operations performed on the database. In this article, I will show you how to perform C (R) UD with Doctrine.

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.

Databases and Doctrine (Symfony 2.5 Book), You can also persist data to MongoDB using Doctrine ODM library. 1 2. $ php app/console doctrine:database:drop --force $ php app/console Doctrine allows you to work with databases in a much more interesting way than just fetching rows of The DQL syntax is incredibly powerful, allowing you to easily join between  Symfony provides all the tools you need to use databases in your applications thanks to Doctrine, the best set of PHP libraries to work with databases. These tools support relational databases like MySQL and PostgreSQL and also NoSQL databases like MongoDB. Databases are a broad topic, so the documentation is divided in three articles:

Doctrine Query Language, Doctrine Object Relational Mapper Documentation: Doctrine Query Language. Imagine all your objects lying around in some storage (like an object database). to use table names and column names or join arbitrary tables together in a query. Using the NEW operator you can construct Data Transfer Objects (DTOs​)  In this video you can find how to merge two table data using inner join keyword and load that data on web page using php and mysql. How to retrieve a single data/record from database using php

Up & Running With Symfony 4, Up & Running With Symfony 4 - Part 2: Database & Doctrine ORM We will add some Duration: 22:50 Posted: Mar 14, 2018 In the second part of the Symfony 4 series we will implement the Doctrine ORM to create and interact with our MySQL database. We will add some records, fetch them and setup our routes/annotations

How to properly count all the rows from a table with Doctrine in , May 5th 2019; 10.7K; 2 Comments. In this short article, we will explain you how to count how many record are there in a table with a primary key with Doctrine in Symfony 4. Count all rows from a table (repository). In this example, we'll assume that you already have tables in your database and you already  It tells Doctrine to use the category_id column on the product table to relate each record in that table with a record in the category table. Next, since one Category object will relate to many Product objects, the make:entity command also added a products property to the Category class that will hold these objects:

5 Doctrine ORM Performance Traps You Should Avoid, 2. Complex Fetch-Joins in Doctrine Query Language. One of the Doctrine's This often increases the cost of converting database rows to objects (called Hydration) considerably. Doctrine2 DQL with Fetch Join Performance. Using a DQL DELETE statement allows you to delete multiple entities of a type with a single command and without hydrating these entities. This can be very efficient to delete large object graphs from the database. Using foreign key semantics onDelete="CASCADE" can force the database to remove all associated objects internally. This strategy is

Comments
  • I don't think Doctrine provides any more fancy way of doing this.
  • What do you mean fancy? I am just asking because the code does not work as intended. It does not return the user for the languages
  • Don't take this the wrong way but your really need to look just a tiny bit harder at the docs. Lots of examples. Not to mention sof questions.
  • I understand, no problem. I actually managed to create a blog site with login system by reading the docs on Symfony, but doctrine is bit harder to wrap my head around. I find it complex for some reason.
  • Looks like you have a classic OneToMany relationship between your entities. There is a lot of documentation for this. Try here to start.
  • @hidar please provide, is it working solution for your case?
  • I do not recommend to put it on the controller. That would defeat the meaning of the repository classes and make your code more bloated and harder to understand.
  • @RobertoMaldonado totally agree. It is better to place at repository or separated service but main question is not about it.
  • As a rule, you should not have to "think" an answer is correct. You should pretty much know it. Perhaps by testing before posting. Your join is completely messed up. And even if you get that fixed up, the use of an IN clause shows you did not understand the original question.
  • I guess it is not as easy as it seems afterall, because I am getting an error with your code. I tried dozen times to understand and fix the issue but there is always an error like this: [Semantical Error] line 0, col 21 near 'username, language.language': Error: Class App\Entity\Language has no field or association named username
  • @Cerad You could have simply pasted a comment with the answer and you would have helped me, instead of commenting what is right and wrong
  • @hanish incase you are wander, I went ahead and added public $username field to App\Entity\Languge (which I am not sure should be done) and it still throws SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u0_.username' in 'field list' error
  • I assume you have put my function to Languge repository. Please move it to User repository instead, as you have "username" column in User entity, not in Language. I will also suggest you to Paste your entities (User and Language) as well to give us more insight. You entities and table must be synced (have have same columns).
  • I will check this soon. Do I create the query using "createQueryBuilder()"?
  • Please check my another answer bellow with detailed explanation of my idea.