DQL query in symfony (many-to-many relationship)?

Question

symfony

I have 2 entities:
user - users
stock - user shares

<?php
namespace Stocker\StockBundle\Entity;

use FOS\UserBundle\Model\User as BaseUser;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity(repositoryClass="Stocker\StockBundle\Entity\Repository\UserRepository")
 * @ORM\Table(name="fos_user")
 */
class User extends BaseUser
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
    
     /**
     * @ORM\ManyToMany(targetEntity="Userstock", inversedBy="users")
     * @ORM\JoinTable(name="userstocks")
     */
    private $userstocks;

    public function __construct()
    {
        parent::__construct();
        $this->userstocks=new ArrayCollection();
    }

<?php
namespace Stocker\StockBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @ORM\Entity(repositoryClass="Stocker\StockBundle\Entity\Repository\UserstockRepository")
 * @ORM\Table(name="userstock")
 */
class Userstock
{
    /**
     * Constructor
     */
    public function __construct()
    {
        $this->users = new ArrayCollection();
    }
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
    /**
     * @ORM\Column(type="string",length=255)
     */
    protected $stockname;
    /**
     * @ORM\ManyToMany(targetEntity="User", mappedBy="userstocks")
     */
    protected $users;

I can get a list of users with stocks and vice versa, stocks with a list of users.
And how to get a list of stocks that a given user does not have?
In pure sql it looks like this:

SELECT us.stockname
FROM userstock us
LEFT JOIN userstocks u_s ON us.id=u_s.userstock_id AND u_s.user_id=1
WHERE u_s.user_id IS null

But I can't access the userstocks intermediate table because it has no essence. And I can’t create an entity with a link to the userstocks table either, since the table has already been created.
You can, of course, do it through regular SQL, but how to do it through doctrine?
You can also parse in php (take all the shares and subtract those that the user has), but this is also a so-so option. You must immediately get the finished result using DQL.

Answer 1

And how to get a list of stocks that a given user does not have?

To paraphrase. You want a list of shares of all users except for a specific one, no?

SELECT s.stockname FROM Stocker\StockBundle\Entity\Userstock s
WHERE s.user != :user

where :user is the user id.
Generally such a thing. If you want to operate on SQL etc. then operate. Write your own native query etc. And even better - give up the doctrine. It is good when you dive into the subject area and spend more time not on SQL / databases, but on competently building relationships between your entities.
For example, I categorically do not believe that the user needs to know about their promotions. Thus, we can get by with one-way communication, get a profit in terms of simplifying the system and reducing coupling, and also get a small profit in terms of performance.
ps StockBundle is a bad idea. In fact, if you have something like CoreBundle (general bundle) - then you have already lost in terms of dividing the logic into "separate parts". Boundaries are drawn incorrectly.

Answer 2

How to query multiple tables in doctrine like
SELECT t2.* FROM `table1` AS t1, `table2` AS t2, `table_links` AS t3 WHERE t1.id='".$id."' AND t1.id= t3.link1 AND t2.id=t3.link2
?