109

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

Mike
  • 12,359
  • 17
  • 65
  • 86

4 Answers4

286

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

SELECT IDENTITY(c.parent) ...
Jani Hartikainen
  • 42,745
  • 10
  • 68
  • 86
  • 7
    Just found it's documented in http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html – alex88 May 27 '13 at 16:11
  • 2
    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. – eggmatters Jul 27 '16 at 16:08
  • Thank you , that helped me finally build my SELECT IN ( SELECT ) ! – Imeksbank Oct 26 '17 at 12:14
  • 9
    This also works when using the query builder: `$qb->select('identity(c.parent)')`. – scyrma Oct 22 '18 at 02:57
  • 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. – Miro Lehtonen Mar 11 '20 at 15:18
16

Solution using createQueryBuilder:

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

$result = $query->getQuery()->getArrayResult();
user3497021
  • 169
  • 1
  • 2
10

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")
Mirza Selimovic
  • 111
  • 1
  • 4
2

You can change it like this:

 ->select(array('i.id','identity(i.parent) parent','i.nom'))
Mohammed Abdelouahab
  • 69
  • 1
  • 5