Error invalid pathexpression statefieldpathexpression or singlevaluedassociationfield expected

I have the following query:

$query = $em->createQueryBuilder()->select('s', 'COUNT(pictures) AS HIDDEN items')
                  ->from("MainBundle:InstagramShop", 's')
                  ->innerJoin('s.userPictures', 'pictures')
                  ;

      $query->andWhere('s.id > :shopId');
      $query->andWhere('pictures.style = :style');
      $query->andHaving('items >= 4');

and for some reason it gives me the following error:

[Semantical Error] line 0, col 151 near 'style = :style': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

I have an InstagramShop which has a many to one relationship with InstagramShopPictures:

Here’s the entity:

    class InstagramShopPicture
    {

          /**
         * @Exclude()
         * @ORMOneToMany(targetEntity="AppMainBundleEntityInstagramPictureStyle",         
           mappedBy="picture", cascade={"persist","remove"})
         */
         protected $style; 


        /**
         * @Exclude()
         * @ORMManyToOne(targetEntity="InstagramShop", inversedBy="userPictures")
         * @ORMJoinColumn(name="shop_id", referencedColumnName="id", nullable=false, onDelete="CASCADE")
         */
        protected $shop;
    }

and here’s InstagramShop

class InstagramShop
{
     /**
     * @Exclude()
     * @ORMOneToMany(targetEntity="InstagramShopPicture", mappedBy="shop", cascade={"persist"})
     * @ORMOrderBy({"created" = "DESC"})
     */
    protected $userPictures;
}

any idea why?

API Platform version(s) affected: 3.0.0

Description

I’m using graphql. All collection associations getting this error

Graphql query

query getUserTrainingTypes {
  user(id: "/api/users/2") {
    trainingTypes{edges{
        node{
          name,
          id
        }
      }}
  }
}

{
  "type": "https://tools.ietf.org/html/rfc2616#section-10",
  "title": "An error occurred",
  "status": 500,
  "detail": "[Semantical Error] line 0, col 48 near 'users IN (SELECT': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.",
  "class": "Doctrine\ORM\Query\QueryException",
  "trace": [
    {
      "namespace": "",
      "short_class": "",
      "class": "",
      "type": "",
      "function": "",
      "file": "/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php",
      "line": 43,
      "args": []
    },
    {
      "namespace": "Doctrine\ORM\Query",
      "short_class": "QueryException",
      "class": "Doctrine\ORM\Query\QueryException",
      "type": "::",
      "function": "semanticalError",
      "file": "/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php",
      "line": 545,
      "args": [
        [
          "string",
          "line 0, col 48 near 'users IN (SELECT': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected."
        ],
        [
          "object",
          "Doctrine\ORM\Query\QueryException"
        ]
      ]
    },
    {
      "namespace": "Doctrine\ORM\Query",
      "short_class": "Parser",
      "class": "Doctrine\ORM\Query\Parser",
      "type": "->",
      "function": "semanticalError",
      "file": "/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php",
      "line": 861,
      "args": [
        [
          "string",
          "line 0, col 48 near 'users IN (SELECT': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected."
        ],
        [
          "array",
          {
            "value": [
              "string",
              "users"
            ],
            "type": [
              "integer",
              102
            ],
            "position": [
              "integer",
              48
            ]
          }
        ]
      ]
    },
    {
      "namespace": "Doctrine\ORM\Query",
      "short_class": "Parser",
      "class": "Doctrine\ORM\Query\Parser",
      "type": "->",
      "function": "processDeferredPathExpressions",
      "file": "/var/www/html/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php",
      "line": 318,
      "args": []
    },
    ...

How to reproduce

My entities

// entity/User.php
#[ApiResource(
    operations: [
        new Get(),
        new Post(security: 'is_granted("IS_AUTHENTICATED_ANONYMOUSLY")'),
        new GetCollection(),
        new Put(),
    ],
    normalizationContext: ['groups' => ['User:read'], 'enable_max_depth' => true],
    denormalizationContext: ['groups' => ['User:write']],
    graphQlOperations: [
        new Query(
            normalizationContext: ['groups' => ['User:read'], 'enable_max_depth' => true],
            denormalizationContext: ['groups' => ['User:write'], 'enable_max_depth' => true],
        ),
        new QueryCollection(
            normalizationContext: ['groups' => ['User:read'], 'enable_max_depth' => true],
            denormalizationContext: ['groups' => ['User:write'], 'enable_max_depth' => true],
        ),
        new Mutation(
            normalizationContext: ['groups' => ['User:read'], 'enable_max_depth' => true],
            denormalizationContext: ['groups' => ['User:write'], 'enable_max_depth' => true],
            name: 'create',
            processor: UserDataProcessor::class,
        ),
    ],
    processor: UserDataProcessor::class,
    extraProperties: ['pagination_items_per_page' => 10]
)]
#[ApiFilter(PropertyFilter::class)]
#[UniqueEntity('email')]
#[UniqueEntity('username')]
#[ORMTable(name: '`user`')]
#[ORMHasLifecycleCallbacks]
class User implements UserInterface, PasswordAuthenticatedUserInterface
{

...

    #[ORMId]
    #[ORMGeneratedValue]
    #[ORMColumn(type: 'integer')]
    #[Groups(['User:read', 'UserConnection:read', 'VideoStream:read'])]
    private int $id;

...

    #[ORMManyToMany(
        targetEntity: TrainingType::class,
        mappedBy: 'users',
        orphanRemoval: true
    )]
    #[Groups(['User:read', 'User:write'])]
    #[AssertValid]
    private Collection $trainingTypes;

// entity/training-type.php
#[ORMEntity(repositoryClass: TrainingTypeRepository::class)]
#[ApiResource(
    operations: [new Get(), new GetCollection()],
    normalizationContext: ['groups' => ['TrainingType:read']],
    denormalizationContext: ['groups' => ['TrainingType:write']],
    graphQlOperations: [
        new Query(normalizationContext: ['groups' => ['TrainingType:read'], 'enable_max_depth' => true]),
        new QueryCollection(normalizationContext: ['groups' => ['TrainingType:read'], 'enable_max_depth' => true]),
    ]
)]
class TrainingType
{
    #[ORMId]
    #[ORMGeneratedValue]
    #[ORMColumn(type: 'integer')]
    #[Groups(['User:read','TrainingType:read'])]
    private int $id;

Possible Solution

n/a

Additional Context

$result = $qb->select('csr.id,csr.survey')
->from('EntityClientSurveyRecord', 'csr')
->innerJoin('EntityAbstractClientRecord','cr','WITH','cr.id = csr.id')
->innerJoin('EntityClient','c','WITH','cr.client = c.id')
->where('csr.survey = :id_survey')
->setParameter('id_survey',$id)
->getQuery()
->getResult();

Message: [Semantical Error] line 0, col 18 near 'survey FROM EntityClientSurveyRecord': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Filename: /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php

EntityClientSurveyRecord:
type: entity
table: clients_survey_records

fields:

result:
type: integer
column: result
nullable: false
options:
comment: Client survey current result.

manyToOne:
survey:
targetEntity: EntityAbstractSurvey
joinColumn:
name: id_survey
referenceColumnName: id
nullable: false

surveyShipmentTracking:
targetEntity: EntitySurveyShipmentTracking
joinColumn:
name: id_survey_shipment_tracking
referenceColumnName: id
nullable: false

Решение

$result = $qb->select(['csr.id','s']) // or add column names ['csr.id','s.id','s.title', ...]
->from('EntityClientSurveyRecord', 'csr')
->innerJoin('csr.survey','s')
->innerJoin('EntityAbstractClientRecord','cr','WITH','cr.id = csr.id')
->innerJoin('EntityClient','c','WITH','cr.client = c.id')
->where('s.id = :id_survey')
->setParameter('id_survey',$id)
->getQuery()
->getResult();

$result = $qb->select(['csr.id','s'])
->from('EntityClientSurveyRecord', 'csr')
->innerJoin('csr.survey','s')
->innerJoin('csr.abstractClientRecord','cr')
->innerJoin('cr.client','c')
->where('s.id = :id_survey')
->setParameter('id_survey',$id)
->getQuery()
->getResult();

Другие решения

Других решений пока нет …

Table of contents
• Error: Invalid PathExpression. Must be a StateFieldPathExpression.
• Invalid PathExpression must be a StateFieldPathExpression [duplicate]
• Invalid PathExpression. Must be a StateFieldPathExpression
• Invalid PathExpression. Must be a StateFieldPathExpression
  #187

Error: Invalid PathExpression. Must be a StateFieldPathExpression.

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. Sorry about …

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);
}

    $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%");

Invalid PathExpression must be a StateFieldPathExpression [duplicate]

Invalid PathExpression. Must be a StateFieldPathExpression (1 answer) Closed 4 years ago. I am currently working in a project using Symfony3.4, a project is about …

public function findRoomsNotBookedByCheckInCheckOut(Request $request) {
        $em = $this->getEntityManager();

        $q = $em->createQuery("SELECT r FROM AppBundle:Room r "
        . "WHERE r NOT IN ( "
        . "SELECT b.room FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut <= :checkIn "
        . "OR b.checkIn >= :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', "'".$request->query->get('check-out')."'")
        ->setParameter('checkOut', "'".$request->query->get('check-in')."'");

        die($q->getSQL());
        return $q->getResult();
    }  

private function roomsByCheckInCheckOut(Request $request, $em) {
        if ($request->query->get('check-in') != null && $request->query->get('check-out') != null) {

           $rooms = $em->getRepository('AppBundle:Room')->findRoomsNotBookedByCheckInCheckOut($request);

        }
        return null;
    }  

[Semantical Error] line 0, col 57 near 'room FROM AppBundle:Bookings': Error: Invalid PathExpression. Must be a StateFieldPathExpression.  

public function findRoomsNotBookedByCheckInCheckOut(Request $request) {
        $em = $this->getEntityManager();

        $q = $em->createQuery("SELECT r FROM AppBundle:Room r "
        . "WHERE r NOT IN ( "
        . "SELECT b.room.id FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut <= :checkIn "
        . "OR b.checkIn >= :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', "'".$request->query->get('check-out')."'")
        ->setParameter('checkOut', "'".$request->query->get('check-in')."'");

        die($q->getSQL());
        return $q->getResult();
    }

[Semantical Error] line 0, col 62 near 'id FROM AppBundle:Bookings': Error: Class AppBundleEntityBookings has no field or association named room.id

public function findRoomsNotBookedByCheckInCheckOut(Request $request) {
        $em = $this->getEntityManager();

        $q = $em->createQuery("SELECT r FROM AppBundle:Room r "
        . "WHERE r NOT IN ( "
        . "SELECT b FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut <= :checkIn "
        . "OR b.checkIn >= :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', "'".$request->query->get('check-out')."'")
        ->setParameter('checkOut', "'".$request->query->get('check-in')."'");

        die($q->getSQL());
        return $q->getResult();
    }

$q = $em->createQuery("SELECT r.id FROM AppBundle:Room r "
        . "WHERE r.id NOT IN ( "
        . "SELECT IDENTITY(b.room) FROM AppBundle:Bookings b "
        . "WHERE NOT ( "
        . "b.checkOut < :checkIn "
        . "OR b.checkIn > :checkOut "
        . ") "
        . ") ORDER BY r.id")
        ->setParameter('checkIn', $chIn)
        ->setParameter('checkOut', $chOut);

Invalid PathExpression. Must be a StateFieldPathExpression

Doctrine Symfony Query Many to Many Issue : Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected Hot Network Questions

$query = $em->createQuery(
                            'SELECT r ' .
                            'FROM AppBundle:Room r ' .
                            'WHERE r NOT IN ( ' .
                            'SELECT b.room ' .
                            'FROM AppBundle:Bookings b ' .
                            'WHERE NOT ( ' .
                            'b.check_out < :check_in ' .
                            'OR ' .
                            'b.check_in > :check_out ' .
                            ')' .
                            ') ' .
                            'ORDER BY r.id'
                    )
                    ->setParameter('check_in', $request->query->get('check-in'))
                    ->setParameter('check_out', $request->query->get('check-out'));

$query = $em->createQuery(
                            'SELECT r ' .
                            'FROM AppBundle:Room r ' .
                            'WHERE r NOT IN ( ' .
                            'SELECT b.room ' .
                            'FROM AppBundle:Bookings b ' .
                            'WHERE  ' .
                            'b.check_out < :check_in ' .
                            'OR ' .
                            'b.check_in > :check_out ' .
                            ') ' .
                            'ORDER BY r.id'
                    )
                    ->setParameter('check_in', $request->query->get('check-in'))
                    ->setParameter('check_out', $request->query->get('check-out'));

Error: Invalid PathExpression. Must be a StateFieldPathExpression after select query via Doctrine

Error: Invalid PathExpression. Must be a StateFieldPathExpression after select query via Doctrine. line 0, col 11 near ‘fkClient, cac.fkIndividualConsultation,’: Error: Invalid …

    return $this->entityManager
        ->createQueryBuilder()
        ->select('cac.fkClient', 'cac.fkIndividualConsultation', 'cac.dateAdded', 'cac.fkWorker')
        ->from(ClientActivityIndividualConsultationHistory::class, 'cac')
        ->where('cac.dateAdded BETWEEN :startDate AND :endDate')
        ->andWhere('cac . fkWorker = :workerId')
        ->setParameters([
            'startDate' => $dateStart,
            'dateEnd' => $dateEnd,
            'workerId' => $workerId
        ])
        ->getQuery()
        ->getArrayResult();

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping"
                  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                  xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
    <entity name="SepcBundleWebBundleEntityClientActivityIndividualConsultationHistory"
            table="client_activity_individual_consultation_history">
        <indexes>
            <index name="fk_client_activity_individual_consulation_history_worker_id" columns="fk_worker_id"/>
            <index name="fk_client_activity_individual_consulation_history_client_id" columns="fk_client_id"/>
            <index name="fk_client_individual_consulation_history_consultation_id"
                   columns="fk_individual_consultation_id"/>
        </indexes>
        <id name="id" type="integer" column="id">
            <generator strategy="IDENTITY"/>
        </id>
        <field name="dateAdded" type="datetime" column="date_added" nullable="false">
            <options>
                <option name="default">CURRENT_TIMESTAMP</option>
            </options>
        </field>
        <many-to-one field="fkClient" target-entity="Client" fetch="LAZY">
            <join-columns>
                <join-column name="fk_client_id" referenced-column-name="id"/>
            </join-columns>
        </many-to-one>
        <many-to-one field="fkWorker" target-entity="Worker" fetch="LAZY">
            <join-columns>
                <join-column name="fk_worker_id" referenced-column-name="id"/>
            </join-columns>
        </many-to-one>
        <many-to-one field="fkIndividualConsultation" target-entity="CalendarIndividualConsultation" fetch="LAZY">
            <join-columns>
                <join-column name="fk_individual_consultation_id" referenced-column-name="id"/>
            </join-columns>
        </many-to-one>
    </entity>
</doctrine-mapping>

->select('cac')
->from(ClientActivityIndividualConsultationHistory::class, 'cac')
->leftJoin('cac.fkIndividualConsultation', 'fkIndividualC')
->leftJoin('cac.fkClient', 'fkClient')
->leftJoin('cac.fkWorker', 'fkWorker')

   return $this->entityManager
                ->createQueryBuilder()
                ->select('cac')
                ->distinct('cac.fkClient')
                ->from(ClientActivityIndividualConsultationHistory::class, 'cac')
                ->where('cac.dateAdded BETWEEN :startDate AND :endDate')
                ->andWhere('cac . fkWorker = :workerId')
                ->setParameters([
                    'startDate' => $dateStart,
                    'endDate' => $dateEnd,
                    'workerId' => $workerId
                ])
                ->getQuery()
                ->getResult();

SELECT IDENTITY(c.parent) ...

->getResult(AbstractQuery::HYDRATE_OBJECT);

Invalid PathExpression. Must be a StateFieldPathExpression
#187

Must be a StateFieldPathExpression #187. hpmewes opened this issue Jul 9, 2012 · 3 comments Comments. Copy link hpmewes commented Jul 9 line 0, col 87 near …

/**
* @ORMEntity
* @ORMTable(name="scs_bgengine_element_type") 
*/
class ElementType extends Entity {

/* ... */

    /**
    * @ORMManyToOne(targetEntity="ElementType", inversedBy="children")
    * @ORMJoinColumn(name="parent_id", referencedColumnName="id")
    * 
    * @GRIDColumn(field="parent.name", title="ElementType Name")
    * @GRIDColumn(field="parent.children", type="array", title="ElementType Children")
    * 
    * @var ElementType
    */
    protected $parent;

    /**
    * @ORMOneToMany(targetEntity="ElementType", mappedBy="parent")
    * 
    * @var ArrayCollection
    */
    protected $children;

/* ... */

SELECT _a.id, _a.name, _a.value, _a.description, _parent.name as parent::name, _parent.children as parent::children FROM ScsBgEngineBundleCoreBundleEntityElementType _a LEFT JOIN _a.parent _parent 

Next Lesson PHP Tutorial

I have the following query:

$query = $em->createQueryBuilder()->select('s', 'COUNT(pictures) AS HIDDEN items')
                  ->from("MainBundle:InstagramShop", 's')
                  ->innerJoin('s.userPictures', 'pictures')
                  ;

      $query->andWhere('s.id > :shopId');
      $query->andWhere('pictures.style = :style');
      $query->andHaving('items >= 4');

and for some reason it gives me the following error:

[Semantical Error] line 0, col 151 near 'style = :style': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

I have an InstagramShop which has a many to one relationship with InstagramShopPictures:

Here’s the entity:

    class InstagramShopPicture
    {

          /**
         * @Exclude()
         * @ORMOneToMany(targetEntity="AppMainBundleEntityInstagramPictureStyle",         
           mappedBy="picture", cascade={"persist","remove"})
         */
         protected $style; 


        /**
         * @Exclude()
         * @ORMManyToOne(targetEntity="InstagramShop", inversedBy="userPictures")
         * @ORMJoinColumn(name="shop_id", referencedColumnName="id", nullable=false, onDelete="CASCADE")
         */
        protected $shop;
    }

and here’s InstagramShop

class InstagramShop
{
     /**
     * @Exclude()
     * @ORMOneToMany(targetEntity="InstagramShopPicture", mappedBy="shop", cascade={"persist"})
     * @ORMOrderBy({"created" = "DESC"})
     */
    protected $userPictures;
}

any idea why?

I have a many to many relationship in a symfony 2 project and i try to create a left join on it but I have an error. I’ve read hundred of doc and post but I can’t find the solution. Hope you could help me to figure out

Here the error:

[Semantical Error] line 0, col 162 near ‘users = u WHERE’: Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

Here my relationship:

   AdminBundleEntityKeywordNeed:
     ...
manyToMany:
        users:
            targetEntity: User
            mappedBy: keywordNeeds

And the other entity:

    AdminBundleEntityUser:
...
keywordOffers:
            targetEntity: AdminBundleEntityKeywordOffer
            inversedBy: users
            nullable: true
            joinTable:
                name: users_keywordoffer
                joinColumns:
                    user_id:
                        referencedColumnName: id
                inverseJoinColumns:
                    keywordoffer_id:
                        referencedColumnName: id

and finally the sql request I generate:

    SELECT distinct u  FROM AdminBundleEntityNetwork n
INNER JOIN AdminBundleEntityUser u WITH n.startup = u
LEFT JOIN AdminBundleEntityKeywordOffer ko WITH ko.users = u
 WHERE n.network = :oUser
AND (u.lastName like '%blabla%' OR u.firstName like '%blabla%' OR u.company like '%blablaa%')
AND ko in ('7','6')

Thanks for your help

You can just use field name as a glue for join insetad of using WITH:

SELECT distinct u FROM AdminBundleEntityNetwork n
INNER JOIN AdminBundleEntityUser u WITH n.startup = u
LEFT JOIN u.keywordOffers ko
...

In fact With clause is useful when you want to add another joining constraint or to join two not related entities. If Network is also connected to users on startup field you can also enhance you DQL:

SELECT distinct u FROM AdminBundleEntityNetwork n
INNER JOIN n.startup u
LEFT JOIN u.keywordOffers ko
...

I hope this helps.

398 votes

0 answers

Get the solution ↓↓↓

return $this->createQueryBuilder('e')
            ->leftJoin('e.entreprise_id', 's')
            ->leftJoin('s.clients', 'c')
            ->andWhere('e.entreprise_id = :val')
            ->setParameter('val', $value)
            ->getQuery()
            ->getArrayResult()
        ;

Write your answer

Share solution ↓

Additional Information:

Link To Source

Link To Answer
People are also looking for solutions of the problem: mysqli::real_connect(): (hy000/2002): connection refused

Similar questions

Find the answer in similar questions on our website.