CODEWITHSUNDEEP
symfonydoctrine-orm
whitebear
whitebear

Reputation: 12433

How to use count for manytomany two tables

I have two class which have relation ship as manytomany

Book table

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

/**
 *
 * @ORM\ManyToMany(targetEntity="Acme\TopBundle\Entity\AuthorData",inversedBy="authorIds")
 * @ORM\JoinTable(name="author_book")
 * )
 */
private $authorIds;

Author table

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

/**
* @ORM\ManyToMany(targetEntity="BookData", mappedBy="authorIds", cascade={"persist"})
*/
private $bookIds;

One book row can have multiple $authorID.

I got the authors list who has published at least one book.

$query = $em->createQuery(
        "SELECT a FROM AcmeTopBundle:AuthorData a JOIN a.bookIds");

But I want to have how much books do they release.

I think I should use count sentence,though,I am not sure it is possible to use count

when you use two tables.

How can you make it?

Upvotes: 1

Views: 92

Answers (2)

praxmatig
praxmatig

Reputation: 263

I can think of 2 solutions for this.

The first one is to save the books_count in author table.

class Author
{
    @ORM\Column(type="integer")
    private $booksCount;

    public function __construct()
    {
         $this->booksCount = 0;
    }
}

If you use this solution, you have to make sure to increment and decrement the booksCount when you add or remove a book.

The second solution is to use DQL

$query = $em->createQuery('SELECT a, COUNT(b.id) FROM AcmeTopBundle:AuthorData a JOIN a.bookIds b GROUP BY b.id');
$result = $query->getScalarResult();

You should use the first solution if you really care about the performance. Otherwise, the second solution is more than enough.

Upvotes: 2

keyboardSmasher
keyboardSmasher

Reputation: 2811

Once you have your AuthorData object, just call something like $authorData->getBookIds()->count()

count is a method of Doctrine\Common\Collections\ArrayCollection (which just uses php's count)

I'm assuming that you have your Entities setup correctly, with proper getters, setters, removers and constructors...

public function __construct()
{
    $this->bookIds = new ArrayCollection();
}

...etc.

Upvotes: 2

Related Questions