Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Question

I'm working on a symfony project entity with query builder. When I try to run this function I get this issue.

[Semantical Error] line 0, col 9 near 'category FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

public function json_filterAllproductsAction() {

    $search = "";
    $category = 1;

    //Combine tables and create the query with querybuilder
    $em = $this->container->get('doctrine.orm.entity_manager');

    $qb = $em->createQueryBuilder();

    $qb->select('p.category')
            ->from('EagleAdminBundle:Products', 'p')
            ->orderBy('p.id', 'DESC');
    if ($category != 0) {
        $qb->andWhere('p.category = :category')
                ->setParameter('category', $category);
    }
    $qb->andWhere('p.productTitle LIKE :title')
            ->setParameter('title', "$search%");

    //convert to json using "JMSSerializerBundle"
    $serializer = $this->container->get('serializer');
    $jsonproducts = $serializer->serialize($qb->getQuery()->getResult(), 'json');
    return new Response($jsonproducts);
}

I think error is in,

$qb->select('p.category')

It would be great help someone can help me.

Micropyle

Answer 1

You need to fetch category as well in your join. Something like this should work fine:

    $qb->select('p', 'c')
        ->from('EagleAdminBundle:Products', 'p')
        ->orderBy('p.id', 'DESC')
        ->join('p.category', 'c');

    if ($category != 0) {

        $qb->andWhere('p.category = :category')
            ->setParameter('category', $category);
    }

    $qb->andWhere('p.productTitle LIKE :title')
        ->setParameter('title', "$search%");

Note if you don't want to limit your search to only products that have categories you can change the join to a leftJoin.

Also note you can have the serializer configured to serialize the category property of product. Then you should just be able to fetch a product and have it automatically serialize the category for you.

Extravasate

Hi I tried this but I need only categories ($qb->select('c')). When I try to do these I'm having this issue. [Semantical Error] line 0, col -1 near 'SELECT c FROM': Error: Cannot select entity through identification variables without choosing at least one root entity alias.. And Product table has a foreign key to category table – Micropyle

Sorry about the slow reply, been away on holiday. You can select category as the root alias (->from('EagleAdminBundle:Category', 'c') then join product to that to restrict your query by product title (->join('c.products', 'p'), should work fine then. – Extravasate