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.

Answer 1

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;