Symfony2 count entity fields that relate to another entity

Question

I have got 2 entities - Authors and Books, 1 author may have many books. I want to show in a table how many books EACH author has (different number per each author). I`ve already seen this question, also this, and this and tried this, as I thought it would be more elegant solution:

<td>{{books|length}}</td>

but every time I get the total number of books for ALL authors. In my controller I get the books like this:

$query = $em->createQuery('SELECT b FROM AB\ProjectBundle\Entity\Books u WHERE b.authorid in (:authorids)');
$query->setParameter('authorid',$authorids);
$books = $query->getResult();

and authors are selected like this:

$query = $em->createQuery('SELECT a FROM AB\ProjectBundle\Entity\Authors a');
$authorids = $query->getResult();

EDIT: My twig loop

<tbody>
            {% for authors in author %}
        <tr>
            <td>{{ authors.name }}</td>
            <td>{{ authors.isactive }}</td>         
            <td>{{ books.authorid|length}}</td>
        </tr>
            {% endfor %}
</tbody>

EDIT 2 My Author entity

class Author
{
    /**
     * @var integer
     */
    private $id;

    /**
     * @var string
     */
    private $name;

    /**
     * Set name
     *
     * @param string $name
     * @return string
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getName()
    {
        return $this->name;
    }
    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }
}

EDIT 3 Books entity

<?php

namespace AB\ProjectBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * 
 */
class Books
{
    /**
     * @var integer
     */
    private $id;

    /**
     * @var string
     */
    private $name;

    /**
     * @var \AB\ProjectBundle\Entity\Author
     */
    private $authorid;

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set Authorid
     *
     * @param \AB\ProjectBundle\Entity\Author $authorid
     * @return Author
     */
    public function setAuthorid(\AB\ProjectBundle\Entity\Author $authorid = null)
    {
        $this->authorid = $authorid;

    return $this;
    }

    /**
     * Get Authorid
     *
     * @return \AB\ProjectBundle\Entity\Author
     */
    public function getAuthorid()
    {
        return $this->authorid;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return string
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }
}

There is no annotations, entities are mapped in *.orm.yml files. Book.orm.yml:

AB\ProjectBundle\Entity\Books:
type: entity
table: Books
id:
    id:
        type: integer
        nullable: false
        unsigned: false
        id: true
        generator:
            strategy: IDENTITY
fields:
    name:
        type: text
        nullable: false

manyToOne:
    authorid:
        targetEntity: Author
        cascade: {  }
        mappedBy: null
        inversedBy: null
        joinColumns:
            authorid:
                referencedColumnName: id
        orphanRemoval: false
lifecycleCallbacks: {  }

Author.orm.yml

AB\ProjectBundle\Entity\Author:
type: entity
table: Author
id:
    id:
        type: integer
        nullable: false
        unsigned: false
        id: true
        generator:
            strategy: IDENTITY
fields:
    name:
        type: text
        nullable: false
lifecycleCallbacks: {  }

Answer 1

You don't need to fetch all books for this so if you don't use the array of all the books in the system somewhere else on this page I'd remove that query (and view param).

Then I'd start to clean up some naming.

AB\ProjectBundle\Entity\Books:
...
manyToOne:
    authorid:
        targetEntity: Author

An entity is a recipe for how your app will handle one thing, so we'll go with singular names

When you create relations in Doctrine you (usually) don't need to set up your own join tables or think about foreign keys (ids) at all. The relations make much more sense to name by entity (as they do in the docs)

So the above should be

AB\ProjectBundle\Entity\Book:
...
manyToOne:
    author:
        targetEntity: Author

Your Author entity is missing a relation to the Book entity

AB\ProjectBundle\Entity\Author:
...
oneToMany:
    books:
        targetEntity: Book

Note how we in one entity has a relation with a singular name, and in the other we have a relation with a plural name. These names simply makes sense as one author can have many books, but one book can only belong to one author.

So in your app (if you generate entities out of these mappings) you will get methods like $book->getAuthor() and $author->getBooks(), which properly describe what they do and what you can expect to get returned.

---

With the relations in place this should be as simple as:

Select the authors you want:

$query = $em->createQuery('SELECT a, b FROM AB\ProjectBundle\Entity\Authors a JOIN a.books b');
$authors = $query->getResult();

Note $authors instead of $authorids - again since we get authors and not an array of ids. We join in the books (via the relation in the Author entity called "books") to avoid forcing Doctrine to run one or more separate queries later on (relations are lazy loaded if not joined).

Then in Twig we simply do

 <tbody>
        {% for author in authors %}
    <tr>
        <td>{{ author.name }}</td>
        <td>{{ author.isactive }}</td>         
        <td>{{ author.books|length}}</td>
    </tr>
        {% endfor %}
 </tbody>

Last bit of naming changed here {% for authors in author %} was simply the wrong way around. It might seem trivial or silly to point out, but writing clean and understandable code is cruical to help others (and yourself) to understand what's actually going on. If I read "authors" then I'm expecting an array of authors, not an object containing the info of one author.