sqljoindoctrine-ormdqlmaterialized-path-pattern

SQL to Doctrine's QueryBuilder conversion

I have ManyToMany relationship between Room and Rights entities. Doctrine automatically created third "room_rights" table. Rights are hierarchical and I use Materialized Path approach to store them in DB, so "rights" table has "path" field.

I'd like to get Rooms that have Rights with path like one I supply as argument. I can do it with SQL query:

select ro.* 
from  
    rooms as ro, 
    room_rights as rr, 
    rights as ri 
where 
    rr.rights_id = ri.id and 
    rr.room_id = ro.id and 
    ( 
        ri.path like '99,%' OR
        ri.path like '100,102,%'
    )

and this works when I run in on the DB. Now, I must to make it using Doctrine's QueryBuilder but I have no idea how to do it when there is join table. Could you help?

Solution

  • So, taking a fresh start today, I managed to solve this issue. Turns out the QueryBuilder expression is quite simple:

    $filter = "(ri.path like '99,%' OR ri.path like '100,102,%')"; // of course in-app it is constructed dynamically

$em->createQueryBuilder()
   ->select('ro')
   ->from('AppBundle\Entity\Room', 'ro')
   ->innerJoin('ro.rights', 'ri', 'WITH', $filter)
;