Symfony2 and Doctrine - Error: Invalid PathExpression. Must be a StateFieldPathExpression

Question

I have an entity that looks like this:

/**
 * @Gedmo\Tree(type="nested")
 * @ORM\Table(name="categories")
 * @ORM\Entity()
 */
class Category extends BaseCategory
{

    /**
    * @ORM\OneToMany(targetEntity="Category", mappedBy="parent")
    */
    protected $children;

    /**
    * @Gedmo\TreeParent
    * @ORM\ManyToOne(targetEntity="Category", inversedBy="children")
    * @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onDelete="SET NULL")
    */
    protected $parent;

}

and I am trying to run a query like this:

$qb = $this->em->createQueryBuilder()
            ->select('c.parent')
            ->from('Category', 'c');

$result = $qb->getQuery()->getArrayResult();

However, I am getting the following error:

[Semantical Error] ... Error: Invalid PathExpression. Must be a StateFieldPathExpression. 

How can I select the parent_id field from my table. I have tried a bunch of variations and even if I do something like this:

$qb = $this->em->createQueryBuilder()
            ->select('c')
            ->from('Category', 'c');

I get all fields in the table except for the parent_id. This seems like Doctrine is getting in the way. How can I query for this parent_id field? or better yet how can I get all fields in the table including the parent_id

Amargo

Answer 1

You can use the currently undocumented IDENTITY function to select the FK IDs in a query:

SELECT IDENTITY(c.parent) ...

Bedivere

Just found it's documented in docs.doctrine-project.org/projects/doctrine-orm/en/latest/… – Cheekpiece

This returns the fk id for that entity. How would you hyrdate that entity? $query->getResult(\Doctrine\ORM\Query::HYDRATE_OBJECT); (The default parameter) is apparently overridden by the identity function. – Landgrave

Thank you , that helped me finally build my SELECT IN ( SELECT ) ! – Extracurricular

This also works when using the query builder: $qb->select('identity(c.parent)'). – Shutin

This is great when the tables are huge. The point is that you don't want to hydrate anything unnecessary and you definitely don't want to do any joins between huge tables. You just want to get the IDs of the foreign key and then use those in a separate query. – Viscera

a complete example would help a lot. – Scotney

Answer 2

Solution using createQueryBuilder:

$query->SELECT('pa.id')
        ->from('Category', 'ca');
$query->join('ca.parent', 'pa');

$result = $query->getQuery()->getArrayResult();

Niggard

Answer 3

You are selecting an object that is not joined. Like said in another answer, you have to do something like :

qb->innerJoin("c.parent", "p")

Quinonoid

Answer 4

You can change it like this:

 ->select(array('i.id','identity(i.parent) parent','i.nom'))

Drillstock