doctrine ORM count arraycollection in where condition

Question

Using Symfony2.8 with Doctrine 2.5, I want to filter in a Doctrine ORM query all datasets where the arraycollection contains exactly 3 elements.

$em = $this->getDoctrine()->getManager();
$query = $em->getRepository("AppBundle:EduStructItem")
->createQueryBuilder('e')
 ->addSelect('COUNT(e.preconditions) AS HIDDEN numberpre')
 ->having('numberpre = 3')
->getQuery();
$res = $query->getResult();
dump($res);
foreach ($res as $entity){
    print "title:".$entity->getTitle()."<br>";
    dump($entity->getPreconditions()->toArray());
}

preconditions is an arraycollection which contains a collection of preconditions.

Finally, I want to get all results having exactly 3 preconditions. Additionally it would be great also to order by the number of values in the arraycollection (something like order by Count(e.preconditions)).

Due to the usage of another bundle I downgraded doctrine from 2.5.2 to 2.5.0. I don't think that this is the reason for my problems but for the sake of completeness here is the doctrine part of my composer's Show:

data-dog/pager-bundle                v0.2.4             Paginator bundle for symfony2 and doctrine orm, allows customization with filters and sorters
  doctrine/annotations                 v1.2.7             Docblock Annotations Parser
  doctrine/cache                       v1.5.2             Caching library offering an object-oriented API for many cache backends
  doctrine/collections                 v1.3.0             Collections Abstraction library
  doctrine/common                      v2.5.2             Common Library for Doctrine projects
  doctrine/data-fixtures               v1.1.1             Data Fixtures for all Doctrine Object Managers
  doctrine/dbal                        v2.5.2             Database Abstraction Layer
  doctrine/doctrine-bundle             1.6.1              Symfony DoctrineBundle
  doctrine/doctrine-cache-bundle       1.2.2              Symfony Bundle for Doctrine Cache
  doctrine/doctrine-fixtures-bundle    2.3.0              Symfony DoctrineFixturesBundle
  doctrine/doctrine-migrations-bundle  1.1.1              Symfony DoctrineMigrationsBundle
  doctrine/inflector                   v1.1.0             Common String Manipulations with regard to casing and singular/plural rules.
  doctrine/instantiator                1.0.5              A small, lightweight utility to instantiate objects in PHP without invoking their constructors
  doctrine/lexer                       v1.0.1             Base library for a lexer that can be used in Top-Down, Recursive Descent Parsers.
  doctrine/migrations                  v1.1.0             Database Schema migrations using Doctrine DBAL
  doctrine/orm                         v2.5.0             Object-Relational-Mapper for PHP             

Here is a test-Entity:

<?php
// src/AppBundle/Entity/EduStructItem.php
namespace AppBundle\Entity;


use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * @ORM\Entity
 * @ORM\Table(name="test_edustructitemcollection")
 */
class EduStructItem
{
    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;


    /**
     * @var string
     * @Assert\NotBlank()
     * @ORM\Column(type="string", length=255, nullable=false)
     */
    private $title;


    /**
     * Preconditions are EduStructItems referencing to an EduStructItem.
     * For a single EduStructItem its empty (which have no subelements).
     * A join table holds the references of a main EduStructItem to its sub-EduStructItems (preconditions)
     *
     * @ORM\ManyToMany(targetEntity="EduStructItem",indexBy="id", cascade={"persist"})
     * @ORM\JoinTable(name="test_edustructitem_preconditioncollection",
     *     joinColumns={@ORM\JoinColumn(name="edustructitem_id", referencedColumnName="id")},
     *     inverseJoinColumns={@ORM\JoinColumn(name="edustructitem_precondition_id", referencedColumnName="id")}
     * )
     */
    public $preconditions;

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

    public function getTitle()
    {
        return $this->title;
    }

    public function setTitle($title)
    {
        $this->title = $title;
    }


    public function getPreconditions()
    {
        return $this->preconditions;
    }

    public function addPrecondition(\AppBundle\Entity\EduStructItem $precondition)
    {
        $this->preconditions->add($precondition);
    }

    public function removePrecondition(\AppBundle\Entity\EduStructItem $precondition)
    {
        $this->preconditions->removeElement($precondition);
    }

}
?>

Finally I always get the error: [Semantical Error] line 0, col 18 near 'preconditions)': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

Now I tried your new solution:

$em = $this->getDoctrine()->getManager();
$query = $em->getRepository("AppBundle:EduStructItem")
    ->createQueryBuilder('e')
    ->addSelect('COUNT(e.preconditions) AS HIDDEN countpre')
    ->join('e.preconditions', 'precondition', Join::WITH)
    ->having('countpre = 1')
    ->getQuery();

and get again the error: [Semantical Error] line 0, col 18 near 'preconditions)': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected. Wenn I write the alias before the HIDDEN I get also: [Semantical Error] line 0, col 53 near 'FROM AppBundle\Entity\EduStructItem': Error: Class 'FROM' is not defined. Consider that it is a self-reflecting relation and there is only one entity but there are two tables. As you can see in my entity's Annotation the self-relations are saved in the test_edustructitem_preconditioncollection - table which has been generated by doctrine due to the annotations.

I tried your latest solution:

$qb = $em->getRepository("AppBundle:EduStructItem")
    ->createQueryBuilder('item');
$qb->addSelect('COUNT(precondition.id) AS countpre HIDDEN ')
    ->join('item.preconditions', 'precondition', Join::WITH)
    ->having('countpre = 1');

When I have countpre before HIDDEN I allways get this error: [Semantical Error] line 0, col 56 near 'FROM AppBundle\Entity\EduStructItem': Error: Class 'FROM' is not defined.

But whenn I put countpre after HIDDEN:

$qb = $em->getRepository("AppBundle:EduStructItem")
            ->createQueryBuilder('item');
        $qb->addSelect('COUNT(precondition.id) AS HIDDEN countpre')
            ->join('item.preconditions', 'precondition', Join::WITH)
            ->having('countpre = 1');

I get the error: An exception occurred while executing 'SELECT t0_.id AS id_0, t0_.title AS title_1, COUNT(t1_.id) AS sclr_2 FROM test_edustructitemcollection t0_ INNER JOIN test_edustructitem_preconditioncollection t2_ ON t0_.id = t2_.edustructitem_id INNER JOIN test_edustructitemcollection t1_ ON t1_.id = t2_.edustructitem_precondition_id HAVING sclr_2 = 1':

SQLSTATE [42S22, 207]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Ungültiger Spaltenname 'sclr_2'.

500 Internal Server Error - DBALException

1 linked Exception: SQLSrvException »

Please Consider that there is only one entity with selfreference and there are these two tables:

USE [easylearndev4_rsc]
GO
/****** Object:  Table [dbo].[test_edustructitemcollection]    Script Date: 14.12.2015 09:31:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test_edustructitemcollection](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [title] [nvarchar](255) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

and

USE [easylearndev4_rsc]
GO
/****** Object:  Table [dbo].[test_edustructitem_preconditioncollection]    Script Date: 14.12.2015 09:32:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
REATE TABLE [dbo].[test_edustructitem_preconditioncollection](
    [edustructitem_id] [int] NOT NULL,
    [edustructitem_precondition_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [edustructitem_id] ASC,
    [edustructitem_precondition_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[test_edustructitem_preconditioncollection]  WITH CHECK ADD  CONSTRAINT [FK_34E716A81B7A6CEB] FOREIGN KEY([edustructitem_precondition_id])
REFERENCES [dbo].[test_edustructitemcollection] ([id])
GO
ALTER TABLE [dbo].[test_edustructitem_preconditioncollection] CHECK CONSTRAINT [FK_34E716A81B7A6CEB]
GO
ALTER TABLE [dbo].[test_edustructitem_preconditioncollection]  WITH CHECK ADD  CONSTRAINT [FK_34E716A85D864668] FOREIGN KEY([edustructitem_id])
REFERENCES [dbo].[test_edustructitemcollection] ([id])
GO
ALTER TABLE [dbo].[test_edustructitem_preconditioncollection] CHECK CONSTRAINT [FK_34E716A85D864668]
GO

Finally I found a workaround by myself:

$em = $this->getDoctrine()->getManager();
$qb = $em->getRepository("AppBundle:EduStructItem")
    ->createQueryBuilder('e');
$qb->join('e.preconditions', 'p', Join::WITH)
    ->groupBy('e.id, e.title')
    ->having('count(p.id) = 1');

But I'm not very happy with this because an arraycollection is already aggregated data why should I join, Count and Group by again! This can't be the idea of Doctrine! Does anyone know a better solution?

Sweetbrier

Answer 1

Try this:

$qb = $this->getDoctrine()->getManager()->getRepository("MyBundle:Groups")
    ->createQueryBuilder('g')
    ->having('count(g.members) = 3')
    ->orderBy('g.members', 'DESC')
; 

Tadzhik

This doesn't work: [Semantical Error] line 0, col 109 near mebers)': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected. An arraycollection is not eccepted as argument for count()! – Sweetbrier

May we see your entities and more particulary the mapping? – Tadzhik

I edited my post and added an example code. The problem is that I can't apply count() to an arraycollection - I wonder why you can! – Sweetbrier

Answer 2

$qb = $this->getDoctrine()->getManager()->getRepository"MyBundle:Groups")
    ->createQueryBuilder('g')
    ->addSelect('COUNT(g.members) AS count HIDDEN')
    ->having('count = 3')
    ->orderBy('count', 'DESC')
    ;

EDIT

After you updated your question, it should be clear that the above solution will not work because your case is you need to count relationship objects and not a single field.

$qb = $em->getRepository("AppBundle:EduStructItem") //Selfreferencing ManyToMany
         ->createQueryBuilder('item');
$qb->addSelect("COUNT(precondition.id) AS count HIDDEN")
   ->join('item.preconditions', 'precondition', Join::WITH)
   ->having('count = 3')
   ->orderBy('count');

Fadiman

This does not work: $qb->addSelect('COUNT('g.members') AS count HIDDEN') has a Syntax error due to the nested quotes. I changes it to $qb->addSelect("COUNT('g.members') AS count HIDDEN") but nevertheless it results in this errormessage: [Syntax Error] line 0, col 101: Error: Expected Literal, got 'count' – Sweetbrier

with the updated code I get the error: [Semantical Error] line 0, col 101 near 'count = 3': Error: 'count' is not defined. It seems that a hidden value it not accessible in the where condition. – Sweetbrier

there is on where condition, it is having. I have this code already working in many projects. Maybe your context has something different. – Fadiman

Please consider updated question text. An example is available now. – Sweetbrier

@Sweetbrier please look at the updated answer. Tell me if this solves your problem – Fadiman

Your updated answer doesn't work. Look at my updated question to see the results. – Sweetbrier

Your new solution does not work. Check updated question, please. – Sweetbrier

Finally I found a workaround by myself but I'm not very happy with it. Check updated question, please. – Sweetbrier