Doctrine - [Semantical Error] - Error: Class has no field or association

Question

I am trying to run a query with Doctrine's DQL within a Repository and I am getting the following error:

QueryException in QueryException.php line 63: [Semantical Error] line 0, col 100 near 'test_suite_id': Error: Class Application\Model\Entity\Page has no field or association named test_suite_id

Associations

A User can have multiple TestSuites. A TestSuite can have multiple Pages. As a result, I have a One-To-Many association between User and TestSuite, and TestSuite and Page respectively.

Here are my entities with only the relevant associations shown:

User

/**
 * @ORM\Table(name="users", uniqueConstraints={@ORM\UniqueConstraint(name="unique_email", columns={"email"})})
 * @ORM\Entity(repositoryClass="Application\Model\Repository\UserRepository")
 */
class User
{
    /**
     * @var TestSuite[]
     *
     * @ORM\OneToMany(targetEntity="TestSuite", mappedBy="user", cascade={"all"})
     */
    private $testSuites = [];
}

TestSuite

/**
 * @ORM\Table(name="test_suites")
 * @ORM\Entity(repositoryClass="Application\Model\Repository\TestSuiteRepository")
 */
class TestSuite
{
    /**
     * @var User
     *
     * @ORM\ManyToOne(targetEntity="User", inversedBy="testSuites")
     * @ORM\JoinColumn{name="user_id", referencedColumnName="id")
     */
    private $user;

    /**
     * @var Page[]
     *
     * @ORM\OneToMany(targetEntity="Page", mappedBy="testSuite", cascade={"all"})
     */
    private $pages = [];

}

Page

/**
 * @ORM\Table(name="pages")
 * @ORM\Entity(repositoryClass="Application\Model\Repository\PageRepository")
 */
class Page
{
    /**
     * @var TestSuite
     *
     * @ORM\ManyToOne(targetEntity="TestSuite", inversedBy="pages")
     * @ORM\JoinColumn(name="test_suite_id", referencedColumnName="id", nullable=false)
     */
    private $testSuite;
}

The Repository DQL Query

public function findPageForUser(User $user, $pageId)
{
    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb->add('select', 'p')
       ->from('Application\Model\Entity\Page', 'p')
       ->join('Application\Model\Entity\TestSuite', 'ts', 'WITH', 'p.test_suite_id = ts.id')
       ->join('Application\Model\Entity\User', 'u', 'WITH', 'ts.user_id = u.id')
       ->where('p.id = :pageId')
       ->andWhere('u = :user')
       ->setParameter('user', $user)
       ->setParameter('pageId', $pageId);

    return $qb->getQuery()->getResult();
}

Please note, I'm only passing in the User and the $pageId here. It is up to the query itself to resolve whether or not the user, via the test suite join, has that specific page.

As you can see, the Page entity clearly has a test_suite_id foreign key column pointing to the id column of the TestSuite entity, and my schema reflects this.

The Generated Query

SELECT p FROM Application\Model\Entity\Page p INNER JOIN Application\Model\Entity\TestSuite ts WITH p.test_suite_id = ts.id INNER JOIN Application\Model\Entity\User u WITH ts.user_id = u.id WHERE p.id = :pageId AND u = :user

Other posts have suggested the following:

• Clear the cache - I have no caching layer enabled
• Restart Apache - It made no difference
• Rename "Colum" to "Column" - I'm not that daft
• Put the FQDN in targetEntity - They're in the same directory so no need

Why am I getting this issue, specifically, and what can I do about it?

Answer 1

DQL is statically typed, and the DQL syntax is about object properties, and not SQL columns.

Additionally, there is no need to select the associated entities directly: joins are automatically put in place by the DQL query component, and it correctly generates the required ON clauses in the outcome SQL.

Here is a version of your query that should work:

SELECT
    p
FROM
    Application\Model\Entity\Page p
INNER JOIN
    p.testSuite AS ts
INNER JOIN
    ts.user u
WHERE
    p.id = :pageId
    AND u = :user

Since Application\Model\Entity\TestSuite#$user is a to-one association, you can further simplify the query by removing one join condition:

SELECT
    p
FROM
    Application\Model\Entity\Page p
INNER JOIN
    p.testSuite AS ts
WHERE
    p.id = :pageId
    AND ts.user = :user

The associated DQL builder code for the latter of the above queries is as follows:

public function findPageForUser(User $user, $pageId)
{
    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb->add('select', 'p')
       ->from('Application\Model\Entity\Page', 'p')
       ->join('Application\Model\Entity\TestSuite', 'ts')
       ->where('p.id = :pageId')
       ->andWhere('ts.user = :user')
       ->setParameter('user', $user)
       ->setParameter('pageId', $pageId);

    return $qb->getQuery()
              ->setFetchMode("Application\Model\Entity\Page", "elements", "EAGER")
              ->getResult();
}