Query a ManyToMany relation and display the good result in Symfony with 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 %}

Probably WHERE a.categories = c.id should be a.categories = c. Take a look at the answer of this question: http://stackoverflow.com/questions/21226545/invalid-pathexpression-statefieldpathexpression-or-singlevaluedassociationfield — Matheno, Jan 27 '15 at 16:56
Your suggestion does not match. Moreover, the fact is that I have a ManyToMany relation. That's why it gives me the same error. — , Jan 27 '15 at 17:02
DQL is a bit different than SQL especially with joins. http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html#joins. I would also suggest dropping users and your where conditions just until you see how dql works. — Cerad, Jan 27 '15 at 17:05
@Cerad, I also tried the JOIN like this: `$queryAdverts = $em->createQuery('SELECT a FROM MySpaceMyBundle:Adverts a, MySpaceMyBundle:Users u JOIN MySpaceMyBundle:Categories c WHERE a.categories = c.id AND a.users = a.id ');` And I have exactly the same error — , Jan 27 '15 at 17:10
Why are you using a repo when you could use a getter on the entity directly? — Alessandro Lai, Jan 27 '15 at 17:17
@Jean, look at my Update in my question. If I use a **dql/query** in my _controller_ is because I need to put all displaying result in a `` in my twig. What do you suggest exactly? — , Jan 27 '15 at 18:12
Consider trying the join using the syntax as shown in the documentation. — Cerad, Jan 27 '15 at 20:21

Alessandro Lai · Accepted Answer · 2015-01-28T17:37:59.617

4

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

edited Jan 28 '15 at 17:37

answered Jan 28 '15 at 11:25

Alessandro Lai

2,254
2
24
32

I already try, and in fact I have **this error** when I do that: `An exception has been thrown during the rendering of a template ("Catchable Fatal Error: Object of class Doctrine\ORM\PersistentCollection could not be converted to string in My\Path\Of\My\Project\app\cache\dev\twig\bf\66\146a31a62bf6f2a549d2604fb5be9c4530ab760a10169af08e8a5b72e9ee.php line ...") in MySpaceMyBundle:MyFolder:indexAdverts.html.twig at line ...`. **The line causing this error is where I insert in my :** ``.
{{ adverts.categories }}
– Jan 28 '15 at 11:51
1

That's because you're trying to print the full relation instead of cycling it over. I'll edit the answer including a template example – Alessandro Lai Jan 28 '15 at 17:34

score 0 · Answer 2 · answered Jan 27 '15 at 17:00

0

WHERE a.categories = c.id

This line should cause the error. I think in your case, you should use a.categories.id instead of a.categories. You can't equalize an object with an integer.

answered Jan 27 '15 at 17:00

Matheno

4,112
6
36
53

I try to of course, but if I make like you suggest I have this error: `[Syntax Error] line ..., col ...: Error: Expected =, <, <=, <>, >, >=, !=, got '.'` – Jan 27 '15 at 17:05
3

Nope. DQL is not SQL. – Cerad Jan 27 '15 at 17:06

Query a ManyToMany relation and display the good result in Symfony with Doctrine

2 Answers2

Linked