Retrieve children from self-referencing entity with certain condition in Doctrine 2

Question

I have an entity Post and it has, among others, the properties parent, children and published. The property parent is a manyToOne relationship and children a oneToMany relationship to itself. The published property is a boolean.

I want to get a Post with a certain ID, with the children that are published, so have published set to true.

I want to achieve this with the QueryBuilder or in DQL. I tried the following, but it still returned the not-published children as well.

    $dql = $this->getEntityManager()->createQuery('
        SELECT
            p
        FROM
            SomeBundle\Entity\Post p
        LEFT JOIN
            p.children pc WITH pc.published = :published
        WHERE
            p.id = :id
    ');

    $dql->setParameters(array(
        'id' => $id,
        'published' => true
    ));

Answer 1

Doctrine SQL Filters are your friend.

http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/filters.html http://symfony.com/doc/current/reference/configuration/doctrine.html

Example: Put a class in your project (for example in filters)

use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Query\Filter\SQLFilter;

class PublishedFilter extends SQLFilter
{

/**
 * @param ClassMetadata $targetEntity
 * @param string        $targetTableAlias
 *
 * @return string
 */
public function addFilterConstraint(ClassMetadata $targetEntity, $targetTableAlias)
{
    if (!array_search('published', $targetEntity->getFieldNames())) {
        return '';
    }

    return $targetTableAlias.'.published = true';

}
}

Now you just need to add that filter in your doctrine config (see http://symfony.com/doc/current/reference/configuration/doctrine.html). Tadaaaa.wav

Important to know is that if you need to select the filtered stuff (for example in the backend), you need to disable the filter $this->getDoctrine()->getManager()->getFilters()->disable('published');

edit: to explain a bit what it does It adds the (in your example) p.published = true condition into every query if your schema actually has the "published" field.