Doctrine 2 reverse MEMBER OF DQL

Question

I've got Profile class with ManyToMany relation "links" and Link class. I need to build DQL query to get all links for a some Profile without reverse relation (link->profile).

First idea was to simple use "MEMBER OF" but looks like it is not possible without direct relation.

MEMEBER OF is building subquery sql. Maybe there is a way to do something similar?

I can`t just use

SELECT l FROM Profile p LEFT JOIN p.links l WHERE p.user = :user

but i can do:

SELECT e FROM Link WHERE e.id IN (SELECT l FROM Profile p LEFT JOIN p.links l WHERE p.user = :user)

so i have this SQL generated:

SELECT ... FROM Link t0_ 
   WHERE t0_.id IN (
      SELECT t1_.id FROM Profile a2_ 
      LEFT JOIN profile_link p3_ ON a2_.user_id = p3_.profile_user_id 
      LEFT JOIN Link t1_ ON t1_.id = p3_.link_id 
      WHERE a2_.user_id = ?
   )

is there any way to build subquery directly to table profile_link without profile join?

Something like:

SELECT ... FROM Link t0_ 
   WHERE t0_.id IN (
      SELECT l.link_id FROM profile_link l 
      WHERE l.profile_id = :user
   )

P.S. there is no need to use Profile table.

profile_link.profile_id = profile.user_id = user.id = :user

i need a DQL query builder to build more complex query with filter/sorter/grouper support. I cant use native query here or modify Entity class. Maybe some kind of custom DQL function can solve it.

Jannes Botis · Answer 1 · 2019-02-28T16:11:50.243

I believe you are overthinking it.

You can get the links of a Profile entity through the links relationship defined.

I assume your Profile class looks like:

use Doctrine\ORM\Mapping as ORM;

class Profile
{
    /**
     * @var integer
     *
     * @ORM\Column(name="user_id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
     private $id;

     /**
      * @ORM\ManyToMany(targetEntity="Link")
      * @ORM\JoinTable(name="profile_link",
      *      joinColumns={@ORM\JoinColumn(name="profile_user_id", referencedColumnName="user_id")},
      *      inverseJoinColumns={@ORM\JoinColumn(name="link_id", referencedColumnName="id")}
      *      )
      **/
     protected $links;

     public function getLinks()
     {
         return $this->links;
     }

You can achieve what you want by:

loading the Profile entity
getting the links relationship
traversing through the collection returned which depending on the relation fetch mode (eager, lazy or extra lazy) will cause the entities to be fetched if not yet fetched from the above step.

In your controller, just do:

$profile = $em->getRepository('AppBundle\Entity\Profile')->find(1);
$links = $profile->getLinks();
foreach($links as $link) {
    echo $link->getId();
}

This way 2 queries will be executed:

1) To fetch the Profile row, eg:

SELECT t0.user_id AS user_id_1 FROM profile t0 WHERE t0.user_id = ?

2) To fetch the related links, eg:

SELECT t0.id AS id_1 FROM link t0 INNER JOIN profile_link ON t0.id = profile_link.link_id WHERE profile_link.profile_user_id = ?

In the last query, only the mapping table is joined, not the Profiles table. The rows returned do not contain the profile data.

Thanks for reply, you are right. This are right SQL queries, but i need grouping/sorting/pagination support available only with DQL query builder. So i`m looking for DQL solution. — El', Mar 19 '19 at 03:27

score 0 · Accepted Answer · answered Mar 19 '19 at 03:44

I give up.

ManyToMany realtion is simple pair of OneToMany realtions, so i created class to represent it.

class ProfileLink{

    /** 
     * @ORM\Id
     * @ORM\OneToMany(targetEntity="Profile")
     */
    protected $profile;


    /** 
     * @ORM\Id
     * @ORM\OneToMany(targetEntity="Link")
     */
    protected $link;
}

Now i can use DQL without reverse part on Link class

SELECT p FROM ProfileLink LEFT JOIN p.link WHERE p.profile = :user_id;

Doctrine 2 reverse MEMBER OF DQL

2 Answers2