Little innerJoin-Query

Question

Old Query in symfony 1.4 and doctrine 1.2

$user = Doctrine_Query::create()
                ->from('User.u')
                ->innerJoin('u.State s')
                ->where('u.id = ?', $id)
                ->andWhere('u.state_id = ?', $state_id)
                ->fetchOne();

Now my Query in symfony2:

$repository = $this->getDoctrine()
->getRepository('FrontendAccountBundle:User');

$user = $repository->findBy(array(
    'activationId' => $activation_id), 
    array('state' => 3));

My error is comming up:

Unrecognized field: state

What is the problem?

Edit: reformatted code

Update

User-Entity:

namespace Frontend\AccountBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Security\Core\User\UserInterface;

/**
 * User
 *
 * @ORM\Table(name="user")
 * @ORM\Entity
 */
class User implements UserInterface, \Serializable
{

    /**
     * @var string
     *
     * @ORM\Column(name="activation_id", type="string", length=255, nullable=true)
     */
    private $activationId;


    /**
     * @var \State
     *
     * @ORM\ManyToOne(targetEntity="State")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="state_id", referencedColumnName="id")
     * })
     */
    private $state;




    /**
     * Set activationId
     *
     * @param string $activationId
     * @return User
     */
    public function setActivationId($activationId)
    {
        $this->activationId = $activationId;

        return $this;
    }

    /**
     * Get activationId
     *
     * @return string 
     */
    public function getActivationId()
    {
        return $this->activationId;
    }

    /**
     * Set state
     *
     * @param \Frontend\AccountBundle\Entity\State $state
     * @return User
     */
    public function setState(\Frontend\AccountBundle\Entity\State $state = null)
    {
        $this->state = $state;

        return $this;
    }

    /**
     * Get state
     *
     * @return \Frontend\AccountBundle\Entity\State 
     */
    public function getState()
    {
        return $this->state;
    }

    public function __construct()
    {
        $this->isActive = true;
        $this->salt = md5(uniqid(null, true));
    }

    /**
     * @inheritDoc
     */
    public function getUsername()
    {
        return $this->email;
    }
    /**
     * @see \Serializable::serialize()
     */
    public function serialize()
    {
        return serialize(array(
            $this->id,
        ));
    }

    /**
     * @see \Serializable::unserialize()
     */
    public function unserialize($serialized)
    {
        list (
            $this->id,
        ) = unserialize($serialized);
    }
}

User-Entity:

namespace Frontend\AccountBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * State
 *
 * @ORM\Table(name="state")
 * @ORM\Entity
 */
class State
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="state", type="string", length=255, nullable=false)
     */
    private $state;

    /**
     * @var string
     *
     * @ORM\Column(name="description", type="string", length=255, nullable=false)
     */
    private $description;



    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set state
     *
     * @param string $state
     * @return State
     */
    public function setState($state)
    {
        $this->state = $state;

        return $this;
    }

    /**
     * Get state
     *
     * @return string 
     */
    public function getState()
    {
        return $this->state;
    }

    /**
     * Set description
     *
     * @param string $description
     * @return State
     */
    public function setDescription($description)
    {
        $this->description = $description;

        return $this;
    }

    /**
     * Get description
     *
     * @return string 
     */
    public function getDescription()
    {
        return $this->description;
    }
}

Answer 1

The problem is that the variable in the User entity is "state" not "stateId". You must always use the names from the entity, not the database. The join from User to State also needs to be done since the stateId is in the State entity.

When joins are needed you are probably better off using queryBuilder or DQL.

Here's a post about joins in Doctrine 2 queryBuilder: doctrine 2 query builder and join tables

Here's the documentation from the Symfony Book for Doctrine: http://symfony.com/doc/current/book/doctrine.html#entity-relationships-associations

Here's an example from my project that is very similar to your problem:

    $uid = 2;
    $rep = $this->getDoctrine()->getRepository('DevondevTrackRTimeBundle:Activity');
    $q = $rep->createQueryBuilder('a')
        ->select ('a.activityId, a.startTime, a.endTime, u.username')
        ->join('a.login','u')
        ->where('u.id = :uid')
        ->setParameter('uid', $uid)
        ->getQuery();

    $acts = $q->getResult();

If I didn't need anything from the user table the query could be written as

    $uid = 2;
    $rep = $this->getDoctrine()->getRepository('DevondevTrackRTimeBundle:Activity');
    $q = $rep->createQueryBuilder('a')
        ->where('a.login = :uid')
        ->setParameter('uid', $uid)
        ->getQuery();

    $acts = $q->getResult();

This is your query reworked in the same way:

    $rep = $this->getDoctrine()->getRepository('FrontendAccountBundle:User');
    $q = $rep->createQueryBuilder('u')
        ->join('u.state','s')
        ->where ('u.id = :uid')
        ->andWhere ('s.stateId = :sid')
        ->setParameters(array('uid' => $id, 'sid' => $state_id))             
        ->getQuery(); 

    $user = $q->getSingleResult();        

Answer 2

Thanks to Peter to light me up a little bit!!!

If you don't want again a the stupid solution from symfony2(-docs) and doctrine2, because you need much more code than in symfony1.4, like that way http://symfony.com/doc/current/book/doctrine.html#joining-to-related-records. Try my solution.

Here is the result.

        $em = $this->getDoctrine()->getEntityManager();
        $user = $em->createQuery('SELECT u FROM FrontendAccountBundle:User u
              INNER JOIN FrontendAccountBundle:State s
                WHERE
                u.activation_id=:activation_id
                and
                u.state=:state_id
                ')
            ->setParameter('activation_id', $activation_id)
            ->setParameter('state_id', 3)
            ->getSingleResult();