Query Joins & Solving the N+1 Problem

Hi,
I am having an issue using QueryBuilder.
I have a Entity\Social (id, name, icon), Entity\UserSocial (id, user[ManyToOne with User entity], social[ManyToOne with Social Entity]) and then a Entity\UserSocial (id, user, [ManyToOne with User entity], social[ManyToOne with Social Entity], url).
In the SocialRepository I created a new method called findAllWithUserValues, but I cannot seems to be able to get the result I want. If ran a raw query (select social.*, us.url from social LEFT JOIN user_social us on social.id = us.social_id AND us.user_id = 1) I get exactly what I need. When I am editing a user profile, I want all social media to be listed there. Out of all, some are associated with the user, having it filled with the user url.
This is my method:
`public function findAllWithUserValues()
{

return $this->createQueryBuilder('s')
    ->select('s.*', 'us.url')
    ->leftJoin('App:UserSocial', 'us', 'on', 's.id = us.social_id AND us.user_id = 1')
    ->getQuery()
    ->getResult();

}`
If I try to get the DQL, I get the query I want:
"<blockquote>SELECT s., us.url FROM App\Entity\Social s LEFT JOIN App:UserSocial us ON s.id = us.social_id AND us.user_id = 1</blockquote>" but when I try ->getQuery()->getResult(), I get an error which doesn't make any sense: <blockquote>[Syntax Error] line 0, col 9: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got ''</blockquote>

Any Idea?

Hi,
I have a problem to start the project. I update my Linux Ubuntu and after launching
<b>php bin/console server:run</b>
I had
`

			In XmlUtils.php line 50:

Extension DOM is required.

`

So, I preferred to delete the project and downloading and unzipping a new project and launching composer install in the start directory. But, now I have this message:
`
Loading composer repositories with package information
Installing dependencies (including require-dev) from lock file
Your requirements could not be resolved to an installable set of packages.

Problem 1

- Installation request for nexylan/slack v2.0.0 -> satisfiable by nexylan/slack[v2.0.0].
- nexylan/slack v2.0.0 requires ext-mbstring * -> the requested PHP extension mbstring is missing from your system.

Problem 2

- Installation request for symfony/framework-bundle v4.0.14 -> satisfiable by symfony/framework-bundle[v4.0.14].
- symfony/framework-bundle v4.0.14 requires ext-xml * -> the requested PHP extension xml is missing from your system.

Problem 3

- Installation request for symfony/debug-bundle v4.0.4 -> satisfiable by symfony/debug-bundle[v4.0.4].
- symfony/debug-bundle v4.0.4 requires ext-xml * -> the requested PHP extension xml is missing from your system.

Problem 4

- symfony/framework-bundle v4.0.14 requires ext-xml * -> the requested PHP extension xml is missing from your system.
- symfony/maker-bundle v1.4.0 requires symfony/framework-bundle ^3.4|^4.0 -> satisfiable by symfony/framework-bundle[v4.0.14].
- Installation request for symfony/maker-bundle v1.4.0 -> satisfiable by symfony/maker-bundle[v1.4.0].

To enable extensions, verify that they are enabled in your .ini files:

- /etc/php/7.4/cli/php.ini
- /etc/php/7.4/cli/conf.d/10-opcache.ini
- /etc/php/7.4/cli/conf.d/10-pdo.ini
- /etc/php/7.4/cli/conf.d/20-apcu.ini
- /etc/php/7.4/cli/conf.d/20-calendar.ini
- /etc/php/7.4/cli/conf.d/20-ctype.ini
- /etc/php/7.4/cli/conf.d/20-exif.ini
- /etc/php/7.4/cli/conf.d/20-ffi.ini
- /etc/php/7.4/cli/conf.d/20-fileinfo.ini
- /etc/php/7.4/cli/conf.d/20-ftp.ini
- /etc/php/7.4/cli/conf.d/20-gettext.ini
- /etc/php/7.4/cli/conf.d/20-iconv.ini
- /etc/php/7.4/cli/conf.d/20-imagick.ini
- /etc/php/7.4/cli/conf.d/20-json.ini
- /etc/php/7.4/cli/conf.d/20-phar.ini
- /etc/php/7.4/cli/conf.d/20-posix.ini
- /etc/php/7.4/cli/conf.d/20-readline.ini
- /etc/php/7.4/cli/conf.d/20-shmop.ini
- /etc/php/7.4/cli/conf.d/20-sockets.ini
- /etc/php/7.4/cli/conf.d/20-sysvmsg.ini
- /etc/php/7.4/cli/conf.d/20-sysvsem.ini
- /etc/php/7.4/cli/conf.d/20-sysvshm.ini
- /etc/php/7.4/cli/conf.d/20-tokenizer.ini
- /etc/php/7.4/cli/conf.d/20-xdebug.ini

You can also run php --ini inside terminal to see which files are used by PHP in CLI mode.
`

Can you help me please?

My boss like 5 years ago said its bad to select all fields from the query (select * from table) and I always had to specify them. And still trying doing so, to not be "bad" programmer. Also back then when I searched in the internet, also found saying this is bad. And instead said to select each field which you need, even if you need most of them. But I now see in doctrine you and also other coders select all columns without specifying one by one. I would think this could improve performance a bit, by selecting only columns which you need. So I guess my boss was wrong? not sure why in all cases can it be bad to select all fields. If its not a performance issue, it should not be bad? And when there is performance issue, then can specify which ones to select.

I have the exact same problem, the n+1 problem.

I have a category.php entity, which is self referencing:

    /**
     * One Category has Many Categories.
     * @ORM\OneToMany(targetEntity="Category", mappedBy="parent", cascade={"remove"})
     * @ORM\OrderBy({"name" = "DESC"})
     */
    private $children;

    /**
     * Many Categories have One Category.
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="children")
     */
    private $parent;

...

    public function __construct() {
        $this->children = new ArrayCollection();
    }
...

public function getChildren()
    {
        return $this->children;
    }```


And on my /category/list page, doctrine creates a query for every single category, because I use this inside the twig template:

{% for category in categories %}

    ...
    {% for sub_category in category.children %}```

the getChildren method creates a new query for every category item.
The solution seems to be a join with addSelect, BUT this is not possible because it is self referencing, both the mapped_by and inversed_by side is the same table/entity (categories).

<b>How can I prevent doctrine from creating new querys for every item on a ManyToOne relation for a self-referencing table?</b>

<u><b>UPDATE:
</b></u>The join works now, but the <u>problem still exists</u>, doctrine creates a new query for every getChildren Call.
Current source code:

Controller:

    public function index(CategoryRepository $repository)
    {
        $categories = $repository->findAllWithJoin();

        return $this->render('category/index.html.twig', [
            'categories' => $categories,
        ]);
    }

Category Repository:

    /**
     * @return Category[]
     */
    public function findAllWithJoin()
    {
        return $this->getOrCreateQueryBuilder()
            ->leftJoin('c.parent', 'cp')
            ->addSelect('cp')
            ->getQuery()
            ->getResult()
            ;
    }

    private function getOrCreateQueryBuilder(QueryBuilder $qb = null)
    {
        // if qb is passed, return it, otherwise return new query builder
        return $qb ?: $this->createQueryBuilder('c'); // r = table alias for recipe in table
    }

Category Entity:

    /**
     * One Category has Many Categories.
     * @ORM\OneToMany(targetEntity="Category", mappedBy="parent", cascade={"remove"})
     * @ORM\OrderBy({"name" = "DESC"})
     */
    private $children;

    /**
     * Many Categories have One Category.
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="children")
     */
    private $parent;

    public function __construct() {
        $this->children = new ArrayCollection();
    }
...
public function getChildren()
    {
        return $this->children;
    }

Template:

                                {% for category in categories %}
                                        ...
                                        {% for sub_category in category.children %}
                                        ...

Hello,
I was wondering why I can't perform joins from different functions. Let's say, I want to make a selection based on different search parameters, not all of which are mandatory, but they do all require the same join. I want to build this query using several functions which I puzzle together based on the search parameters.

For example, these two functions:

So I have two functions. I call this one first:

    public function addEquipmentSubType(QueryBuilder $builder): QueryBuilder
    {
        return $builder
            ->andWhere(est.code = :code)
            ->leftJoin('q.equipmentSubType', 'est')
            ->addSelect('est')
            ->setParameter('code', '123');
    }

and then this one:

public function search(QueryBuilder $builder): QueryBuilder
{
    return $builder
        ->andWhere('q.projectNumber LIKE :search OR 
            est.name LIKE :search OR ')
}

This triggers an error, saying 'est' is not defined. It seems like the only way to fix this, is to join equipmentSubtype again, but this causes the eventual query to have 2 joins with the same table.

Is there a way to pass those table aliasses?

Again nice course, but have small problem. I try to do similar search function but for Article with Comment (you have Comments with Article).
But, query is just try to search in articles, how have any comments. Exp. if I didn't search any string, I must see my all articles, but now I see article just how has any comments. How to fix it?
public function SearchString(?string $term)
{
$qb = $this->createQueryBuilder('b')
->innerJoin('b.comments', 'c')
->addSelect('c')
;
if ($term) {
$qb->andWhere('b.text LIKE :value OR c.text LIKE :value')
->setParameter('value', '%' . $term . '%')
;
}
return $qb
->orderBy('b.id', 'DESC')
->getQuery()
->getResult()
;
}

If I have search input in another "block.html.twig" not in "index.html.twig", how I can load {{ app.request.query.get('q') }} ? "app" is empty.

How do you avoid additional queries on a list of entities with their relationships? Say, related to this example, I want to list some of the comments on the index of posts, similar to the Facebook news feed. For each post, it performs a separate query on the comments table for each post to get the comments. So if a show 10 posts, it's doing 11 queries.

Eloquent handles this using the 'with' method, which uses an array of post IDs to get all related comments in one query, then it somehow hydrates the relevant post with it's comments. So only 2 queries are needed. Is there a similar way to do this with Doctrine? I'm not sure how I would optimize this manually, since the foreign key ID fields are hidden.

Is there a reason you're using PHPDoc annotations in PHP7? I see that they're more expressive :array vs @return Comments[], but I'm wondering if there's some benefit, ie. from PHPStorm or static analysis or something.