Lost column definitions with joins, in twig

Question

I've got a query set up in doctrine that I'm confident does what it's supposed to...it joins several tables so we can skip a bunch of extra queries to get data through foreign keys:

$posts = $this->getDoctrine()->getManager()
        ->createQuery('
            SELECT a AS article, COUNT(c) AS comments
            FROM ArticleBundle:Article a
                LEFT JOIN CommentsBundle:Comment c WITH (c.article = a.id)
                LEFT JOIN ImageBundle:Image i WITH (i.id = a.image)
            GROUP BY a.id
            ORDER BY a.timestamp DESC
        ')
        ->getResult();

I'm able to access the data quite succesfully, like so:

{% for post in posts %}
<div id="news-story">
 <div class="title">{{ post.article.title }}</div>
 <div class="comments">{{ post.comments }}</div>
 ...
{% endfor %}

The issue is, as soon as I add a second column ("i AS image") to the field list, it fails. I get the following message...

Item "article" for "Array" does not exist in ...

...and I can't quite figure out why. I would hope that I would be able to access the variables like {{ post.article.title }} and {{ post.image.path }} (which does exist, by the way)...but I can't.

Any thoughts would be greatly appreciated.

Thanks!

I'm still a beginner to symfony2, but i could tell you that maybe if u dump the post you'll understand the issue, put {{ dump(post) }} http://twig.sensiolabs.org/doc/functions/dump.html — Mohammad AbuShady, May 16 '13 at 02:51
Do you have a relationship between the image entity, and article entity? — Pete Mitchell, May 16 '13 at 02:54
Yep! The article entity has a OneToOne relationship on a column named "image" to the image entity. — mchitten, May 16 '13 at 02:55
tMohammad: Thanks for that. I checked it and, interestingly, it actually looks okay. Very confusing. — mchitten, May 16 '13 at 02:55
See my answer, but in your example, image would be accessed by using `{{ post.article.image.path }}` because it is associated with article, and doctrine will wire up your associations — Pete Mitchell, May 16 '13 at 03:07

Pete Mitchell · Accepted Answer · 2013-05-16T03:04:47.497

I think your confusion arises from the fact that you have created a query that returns a scalar result: COUNT(c)... along with objects.

This means that instead of returning an array of Article entities, as you expect (you are trying to use post.image) you actually get an associative array, where each member contains the count - post.comments and the article entity post.article.

n.b. If you want to access images associated with the article, you will need to use post.article.image.

I'll try and illustrate:

Your expecting an array of article objects that you can iterate over:

array(
    [0] => ArticleEntity,
    [1] => ArticleEntity,
    [2] => ArticleEntity
)

What you actually get from your query

array(
    [0] => array(
        'article' => ArticleEntity,
        'comments'=> 10
    ),
    [1] => array(
        'article' => ArticleEntity,
        'comments'=> 3
    ),
    [2] => array(
        'article' => ArticleEntity,
        'comments'=> 0
    )
)

If you remove the scalar part of your query COUNT(c) AS comments then you will get the first example, leave it in and you will get the second example.

The Doctrine documentation on pure/mixed results is some good reading/explanation on this.

As a side note is there any reason you are writing raw SQL. For a start you can re-write the query using DQL to leverage some of the power Doctrine offers.

$qb = $this->getDoctrine()->getManager()->createQueryBuilder();

$qb
    ->select('article', 'image', 'COUNT(comments) as comment_count')
    ->from('ArticleBundle:Article', 'article')
    ->leftJoin('article.comment', 'comments')
    ->leftJoin('article.image', 'image')
    ->groupBy('article.id')
    ->orderBy('article.timestamp', 'DESC')
;

$posts = $qb->getQuery()->getResult();

Fantastic and very informative. Just what I was looking for. Thanks! — mchitten, May 16 '13 at 03:16

Lost column definitions with joins, in twig

1 Answers1