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

Question

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

Error message:

[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="Sepc\Bundle\WebBundle\Entity\ClientActivityIndividualConsultationHistory"
            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>

I tried everything ( stackoverflow, google, own expierences ) but I really don't know where can be problem. Can somebody explain me this error or navigate me to right way? Thanks.

I do not want to use IDENTITY function because I need all data asociated with foreign keys. ( Doctrine automatically select these objects and I think that this can generate error, but why? )

Using: Symfony FW, Doctrine 2 ORM, MySQL

My tips:

1. Bad ORM generated files
2. QueryBuilder bug

Answer 1

You should join with the fkClient, fkIndividualConsultation and fkWorker relations in order to select them.

So change to:

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

Answer 2

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

Fixed.

Use getResult() function instead if getArrayResult();

Thanks all.

Answer 3

you can user select statement as follows:

SELECT IDENTITY(c.parent) ...

it will return the ids and then you can hydrate it with object in getResult() method as follows:

->getResult(AbstractQuery::HYDRATE_OBJECT);