查询多对多关系并在 Symfony with Doctrine 中显示好的结果 [英] Query a ManyToMany relation and display the good result in Symfony with Doctrine

问题描述

为了知道它是如何真正工作的，有一个 未回答来自 Stack 网站的问题，请注意我有类似的问题.

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

在我的 SQl 数据库中，我有两个表:Adverts 和 Categories

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

确实，Adverts 表可以包含MANY Categories，当然一个Category 可以包含许多广告.

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

所以我在两个表之间有一个 ManyToMany 关系.在 SQL 中，Doctrine 为我创建了一个名为 adverts_categories 的数据透视表.到目前为止没有任何问题，理论上一切都是正确的.

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.

因此，在我的 SQl 数据库中，我有三个表:adverts、adverts_categories 和 categories，如下所示:

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)     |
| ...         | ...         |
+-------------+-------------+

在我的 Symfony 项目中，在我的实体文件夹中，我只有两个实体名称 Adverts.php 和 Categories.php，理论上现在也是正确的.

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.

这是Adverts.php的代码:

class Adverts
{
     /**
     * @var integer
     *
     * @ORMColumn(name="id", type="integer", nullable=false)
     * @ORMId
     * @ORMGeneratedValue(strategy="IDENTITY")
     */
    private $id;

 /**
 * @var Users
 *
 * @ORMManyToOne(targetEntity="Users")
 * @ORMJoinColumns({
 *   @ORMJoinColumn(name="users_id", referencedColumnName="id")
 * })
 */
private $users;

     /**
     * @var DoctrineCommonCollectionsCollection
     *
     * @ORMManyToMany(targetEntity="Categories", inversedBy="adverts")
     * @ORMJoinTable(name="adverts_categories",
     *   joinColumns={
     *     @ORMJoinColumn(name="adverts_id", referencedColumnName="id")
     *   },
     *   inverseJoinColumns={
     *     @ORMJoinColumn(name="categories_id", referencedColumnName="id")
     *   }
     * )
     */
    private $categories;

这里是 Categories.php 的代码:班级分类

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

{
     /**
     * @var integer
     *
     * @ORMColumn(name="id", type="integer", nullable=false)
     * @ORMId
     * @ORMGeneratedValue(strategy="IDENTITY")
     */
    private $id;

     /**
     * @var DoctrineCommonCollectionsCollection
     *
     * @ORMManyToMany(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 ));
    }

错误是:

[语义错误] line ..., col ... 'categories' 附近:错误:无效路径表达式.StateFieldPathExpression 或应为 SingleValuedAssociationField.

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

我真的不明白.有人可以帮忙吗?

I really don't understand. Someone could help?

更新

如果它可以帮助搜索答案，我想在我的树枝 indexAdverts.html.twig 中显示所有结果，这是代码:

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 %}

推荐答案

如果不是真的有必要，您不应该在控制器中使用 DQL 或其他直接查询.你应该这样做:

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 %}

这篇关于查询多对多关系并在 Symfony with Doctrine 中显示好的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！