How to create a query in the entity repository with a many-to-many relationship? Empty query

Question

I have got a many-to-many relationship between advert and document : here are the 3 tables :

advert
document
document_advert.

How can I get the author from Advert and the path from Document? The first attempt does not throw any error, but the query seems empty, I don't understand this from the debugger :

[![enter image description here][1]][1]

[![enter image description here][2]][2]

My goal is something like this in sql :

SELECT d.path, a.author 
FROM document_advert da 
JOIN document d 
JOIN advert a 
WHERE da.advert_id = 1 
AND da.document_id = d.id

My attempt (I am in the AdvertRepository) :

public function getDocNames($id){
     $em = $this->getEntityManager();

    $query = $this->createQueryBuilder('a')
            ->join('a.document', 'd')
            ->addSelect('d')
            ->where("a.id = :id")
            ->setParameter('id', $id)
            ->getQuery()
    ;
    return $query->getResult();
    //issue: empty query

}

---

EDIT : I am trying to get all the values from d, without calling them explicitly (I don't want to do d.path, d.name, etc. but d instead). But ".path" in the twig file is not recognized if I don't add "d.path" in the query.

in twig:

{% for doc in docNames %}
    {{ doc.path }}
{% endfor %}

If I remove .path in the query, ".path" "does not exist " : here is the query in the DQL format :

public function getDocNamesC($id){
        $query = $this->_em->createQuery("SELECT a, d.path FROM OCPlatformBundle:Advert a JOIN a.documents d WHERE a.id = $id");
        return $query->getResult();
    }

Answer 1

Not sure why you defined the $em variable and didn't use it. I assume you were trying to do something like the below.

public function getDocNames($id){
    $qb = $this->getEntityManager()->createQueryBuilder();

    return $qb->select('a.author')
        ->addSelect('d.path')
        ->from('OCPlatformBundle:Advert', 'a')
        ->join('a.documents', 'd')
        ->where(
            $qb->expr()->eq('a.id', ':id')
        )
        ->setParameter('id', $id)
        ->getQuery()
        ->getResult();
}

If you want to get the full entities

public function getDocNames($id){
    $qb = $this->getEntityManager()->createQueryBuilder();

    return $qb->select('a')
        ->addSelect('d')
        ->from('OCPlatformBundle:Advert', 'a')
        ->join('a.documents', 'd')
        ->where(
            $qb->expr()->eq('a.id', ':id')
        )
        ->setParameter('id', $id)
        ->getQuery()
        ->getResult();
}

Then when accessing the result in Twig

{% for advert in docNames %}
    {{ advert.name }}

    {% for document in advert.documents %}
       {{ document.path }}
    {% endfor %}
{% endfor %}

We need to loop through the documents because it's a many-to-many relationship so there isn't one path per advert.