Я использую createQueryBuilder для создания запроса, как это
$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();
И я получаю следующее сообщение Тип: Doctrine ORM Query QueryException
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
Но если я изменю $qb->select('csr.id,csr.survey') за $qb->select('csr.id') оно работает
это файл сопоставления
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
1
Решение
Вам нужно объединить свои отношения, используя их сопоставленные свойства, как для опроса, вам нужно присоединить это в своем объекте построителя запросов.
$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();
Также было бы хорошо, если вы присоединитесь EntityAbstractClientRecord а также EntityClient используя некоторые сопоставленные свойства, как вы уже сделали для опроса, например,
$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();
0
Другие решения
Других решений пока нет …
- 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
This is an article about Symfony and Doctrine.
Each version is as follows.
Symfony 3.4
Doctrine 2.12
Most of the time, Error can be resolved by following the Error message.
This time, it will be a memorandum when the following error is resolved.
[Semantical Error] ... ... : Error: Invalid PathExpression. Must be a StateFieldPathExpression.
The above occurred when I specified the foreign key of the table that was joined to SELECT of ORM.
Error: Invalid PathExpression. Error occurred
An error occurred when I ran the code below.
I have selected p.Tag directly on the joined table.
If I think about it now, it’s natural!
// In Product.php Entity
/**
* @var EccubeEntityTag
*
* @ORMManyToOne(targetEntity="EccubeEntityTag")
* @ORMJoinColumns({
* @ORMJoinColumn(name="tag_id", referencedColumnName="id", nullable=false)
* })
*/
private $Tag;
...
// Query in ProductRepository
$qb = $this->createQueryBuilder('p')
->select([
'p.name',
'p.Tag'
]);
Error: Invalid PathExpression. Error resolution
By using the IDENTITY() function below, it was possible to get it directly.
// Query in ProductRepository
$qb = $this->createQueryBuilder('p')
->select([
'p.name',
'IDENTITY(p.Tag) as tag_id'
]);
However, I noticed it after it was resolved, but the reason why the above error does not usually appear is that JOIN was properly defined by query.
You can also eliminate the error by writing as follows.
// Query in ProductRepository
$qb = $this->createQueryBuilder('p')
->leftJoin('p.Tag', 't')
->select([
'p.name',
't.id as tag_id'
]);
Summary
I was fortunate to discover a new function with an error that occurred in a different way than usual.
I’ve never used IDENTITY() before, but I found it convenient to get it easily.
I referred to the following site. It contains all the information about SELECT.
Doctrine Query Language
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();
Сообщение об ошибке:
[Semantical Error] line 0, col 11 near ‘fkClient,
cac.fkIndividualConsultation,’: Error: Invalid PathExpression. Must be
a StateFieldPathExpression.
ORM:
<?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>
Я пробовал все (stackoverflow, google, собственный опыт), но я действительно не знаю, где может быть проблема. Может ли кто-нибудь объяснить мне эту ошибку или указать правильный путь? Спасибо.
Я не хочу использовать функцию IDENTITY, потому что мне нужны все данные, связанные с внешними ключами. (Doctrine автоматически выбирает эти объекты, и я думаю, что это может вызвать ошибку, но почему?)
Использование: Symfony FW, Doctrine 2 ORM, MySQL
Мои советы:
- Плохие файлы, созданные ORM
- Ошибка QueryBuilder
Перейти к ответу
Данный вопрос помечен как решенный
Ответы
3
Вы должны присоединиться с отношениями fkClient, fkIndividualConsultation и fkWorker, чтобы выбрать их.
Так что измените на:
->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();
Фиксированный.
Вместо этого используйте функцию getResult (), если getArrayResult ();
Спасибо всем.
Вы можете выбрать оператор следующим образом:
SELECT IDENTITY(c.parent) ...
Он вернет идентификаторы, а затем вы можете гидратировать его с помощью объекта в методе getResult () следующим образом:
->getResult(AbstractQuery::HYDRATE_OBJECT);