查询ManyToMany关系并在Symfony中使用Doctrine显示良好的结果

Question

in order to know how it really works, there is an unanswered question from Stack website, and notice that I have the similar problem.

In my SQl database, I have two tables: Adverts and Categories

Indeed, the Adverts table can contain MANY Categories, and of course a Category can be in many Adverts.

So I have a ManyToMany relation between the two tables. in SQL, Doctrine creates me a pivot table named adverts_categories. So far there are no problems , everything is theoretically correct.

So, in my SQl database, I have three tables: adverts, adverts_categories and categories like this:

    adverts
+-------------+--------------+
| id          | int(11)      |
| ...         | ...          |
+-------------+--------------+

    adverts_categories 
+---------------+--------------+
| adverts_id    | int(11)      |
| categories_id | int(11)      |
+---------------+--------------+

    categories
+-------------+-------------+
| id          | int(11)     |
| ...         | ...         |
+-------------+-------------+

And in my Symfony project, in my entity folder I have just the two entities name Adverts.php and Categories.php, which is theoretically correct for now too.

Here's the code for Adverts.php:

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

 /**
 * @var \Users
 *
 * @ORM\ManyToOne(targetEntity="Users")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="users_id", referencedColumnName="id")
 * })
 */
private $users;

     /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Categories", inversedBy="adverts")
     * @ORM\JoinTable(name="adverts_categories",
     *   joinColumns={
     *     @ORM\JoinColumn(name="adverts_id", referencedColumnName="id")
     *   },
     *   inverseJoinColumns={
     *     @ORM\JoinColumn(name="categories_id", referencedColumnName="id")
     *   }
     * )
     */
    private $categories;

And here's the code for Categories.php: class Categories

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

     /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Adverts", mappedBy="categories")
     */
    private $adverts;

So now, when I try to make a query in order to have the results of this request an error occured. Here's the code in my controller:

public function indexAdvertsAction() {

        $em=$this->getDoctrine()->getManager();
        $advert= $em->getRepository('MySpaceMyBundle:Adverts');

$queryAdverts = $em->createQuery('SELECT a
                                    FROM MySpaceMyBundle:Adverts a, MySpaceMyBundle:Users u, MySpaceMyBundle:Categories c
                                    WHERE a.categories = c.id
                                    AND a.users = a.id ');

$advert= $queryAdverts->getResult();

        return $this->render('MySpaceMyBundle:MyFolder:indexAdverts.html.twig', array('advert' => $advert ));
    }

The error is:

[Semantical Error] line ..., col ... near 'categories': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

I really don't understand. Someone could help?

---

UPADTE

if it could help for searching an answer, I would like to display all the result in a in my twig indexAdverts.html.twig, here's the code:

{% for adverts in advert%}
   <tr>
       <td>{{ adverts.id }}</td>
       <td>{{ adverts.name }}</td>
       <td>{{ adverts.users }}</td>
       <td>{{ adverts.categories }}</td>
       <td><a href="{{ path('editAdverts', {'name': adverts.name}) }}"><button class="btn btn-warning btn-xs">Edit</button></a></td>
   </tr>
{% endfor %}

Answer 1

You shouldn't use DQL or others direct queries in your controllers if not really necessary. You should do this:

public function indexAdvertsAction() {
    $em=$this->getDoctrine()->getManager();
    $adverts = $em->getRepository('MySpaceMyBundle:Adverts')->findAll();

    return $this->render(
         'MySpaceMyBundle:MyFolder:indexAdverts.html.twig',
         array('adverts' => $adverts )
    );
}

Then, in your template, the advert entity will take care of the rest, thanks to the correct relations mapping:

{% for adverts in advert%}
   <tr>
       <td>{{ adverts.id }}</td>
       <td>{{ adverts.name }}</td>
       <td>{{ adverts.users }}</td>
       <td>
           {% for category in adverts.categories %}
               {{ adverts.categories }}
           {% endfor %}
       </td>
       <td>
           <a href="{{ path('editAdverts', {'name': adverts.name}) }}"><button class="btn btn-warning btn-xs">Edit</button></a>
       </td>
   </tr>
{% endfor %}