How to set up inheritage child entity as UserInterface with Symfony2

Question

I have a database model with inheritage, using the "Class Table Inheritance" model of symfony2.

An Employee is an abstract class, the top-level inheritance model.

A Secretary is a child extending Employee. A Technician is also a child extending Employee.

I am building an application where secretaries can manage some things for the technicians, so they have an admin panel with some forms etc.

The SecretaryRepository is also a UserProvider interface allowing secretaries to authenticate through the application.

The problem I have is that the authentication query does not care about the discriminator...

The debug of the authentication SQL query

SELECT e0_.*, s1_.*
FROM secretaries s1_
INNER JOIN employees e0_ ON s1_.id = e0_.id
WHERE e0_.removed_at IS NULL AND e0_.is_hidden = ? AND e0_.email = ?

I just do not understand how to manage the relation between Secretary and Employee ? Should I manually include a foreign key from Secretary to Employee ? Or should I manually take care of the discriminator in the authentication query ?

Let's have a look at the basics of my models :

The parent class Employee

abstract class Employee
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

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

    // [...] some other fields
}

The child class Secretary

class Secretary extends Employee
{
    /**
     * @var string
     *
     * @ORM\Column(name="password", type="string", length=255, nullable=true)
     * @Assert\NotBlank()
     */
    private $password;

    /**
     * @var string
     *
     * @ORM\Column(name="salt", type="string", length=40)
     */
    private $salt;

    /**
     * @var array
     *
     * @ORM\Column(name="roles", type="array")
     */
    private $roles;

    // [...] some other fields
}

The UserProviderInterface class SecretaryRepository

class SecretaryRepository extends EntityRepository implements UserProviderInterface
{
    public function loadUserByUsername($username)
    {
        $secretary = null;

        $qb = $this->_em->createQueryBuilder();

        $qb->select('s')
            ->from('HygieCoreBundle:Secretary', 's')
            ->where($qb->expr()->isNull('s.removedAt'))
            ->andWhere($qb->expr()->eq('s.isHidden', ':isHidden'))
            ->andWhere($qb->expr()->eq('s.email', ':email'))
            ->setParameter('isHidden', false)
            ->setParameter('email', $username)
        ;

        try {
            $secretary = $qb->getQuery()->getSingleResult();
        }
        catch (NoResultException $e) {
            throw new UsernameNotFoundException(sprintf('Aucune secrétaire ne possède l\'adresse e-mail : "%s".', $username), 0, $e);
        }

        return $secretary;
    }

    // [...] some other methods
}

The problem is simple, the INNER JOIN clause is not correct because it does not use a foreign key from secretary to employee but the direct id insteed, that can be a technician id too !

Here is an export of some rows of my tables :

INSERT INTO `technicians` (`id`, `speciality`) VALUES
(1, 'Communication')

INSERT INTO `employees` (`id`, `created_at`, `updated_at`, `removed_at`, `is_hidden`, `firstname`, `name`, `entityName`) VALUES
(1, '**sometime**', '**sometime**', NULL, 0, '**TechnicianFirstname**', '**TechnicianName**', 'technician'),
(2, '**sometime**', '**sometime**', NULL, 0, '**SecretaryFirstName**', '**SecretaryName**', 'secretary');

INSERT INTO `secretaries` (`id`, `password`, `salt`, `roles`) VALUES
(1, '**HashedPassword**', '**HashedSalt**', 'O:43:"Doctrine\\Common\\Collections\\ArrayCollection":1:{s:54:"\0Doctrine\\Common\\Collections\\ArrayCollection\0_elements";a:1:{i:0;s:10:"ROLE_ADMIN";}}');

ALTER TABLE `technicians` ADD CONSTRAINT `FK_1DCF6554BF396750` FOREIGN KEY (`id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

ALTER TABLE `secretaries` ADD CONSTRAINT `FK_ACB3412BF396750` FOREIGN KEY (`id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;

Answer 1

You're selecting data directly from secretaries table, so there's no possibility you could accidentally select some technicians. You're safe with this code.

Discriminator will be needed in case you would like to select both, secretaires and technicians in same query, i.e. in case your query would select from employees table.